[Solved] Get max and min of filtered cells

[forgottenBlock]

I've got a table of values, where column A has a bunch of names that i want to search, values are in columm G and the thing to match is in I2 (capital i 2). I can get a sum of the values like this:

=SUMPRODUCT(A2:A23=I2;G2:G23)

And I could get an average by using SUMPRODUCT/COUNTIF. But what I want to get is MAX & MIN.

I've had a play with VLOOKUP but I don't know how to drive that right - plus I think VLOOKUP will only return "a" cell, not a list of cells with which I could then use min/max on.

Or another way to describe what I want to do is create a view on A2:A23-G2-G23 that only includes rows where the "A" column matches what is in I2. Can I do that without needing to use the UI? The reason for that is I've got a lot of variations in column A.

If I was using the GUI approach in Excel, I'd have a filter enabled for the sheet on column A, then select the column value I cared aviyt and then use min/max on the visible rows. That's awful because it is only 1 value at a time and I'd rather do a dozen or so.

[MrProgrammer]

But what I want to get is MAX & MIN.

You can't get max or min with SUMPRODUCT.
[Solved] MINIF and MAXIF Formulas
[Solved] Max/Min from VLOOKUP or SUMPRODUCT

… I want to … create a view …. Can I do that without needing to use the UI? The reason for that is I've got a lot of variations in column A.

You can create a view using this method.
[Tutorial] Sorting and Filtering data with formulas

If you need any additional assistance attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). I will not help further unless you attach. You would ned to explain more about a dozen or so before I could help with that filter usage. Both Standard Filter and Advanced Filter provide for multiple criteria.

I don't understand I cared aviyt.

[RoryOF]

A guess "aviyt" a mistyping of "about" - adjacent letters keyed.

[Villeroy]

Filter the range and apply function SUBTOTAL.

[Alex1]

Here's an example. The minimum and maximum of G for each value of A are determined using a pivottable.
The getpivotdata function in E27 and E28 is used to display the minimum and maximum for the value of A in I2.
This has been created using LibreOffice. OpenOffice cannot use the same column more than once.

[forgottenBlock]

I did some more Googling and found that AOO was my problem... upgrading to LOO v24.something has a MAXIFS function builtin which is exactly what I was looking for. What I wasn't looking for was the UI bugs that came with it. Sigh.

[Hagar Delest]

What kind of UI bugs?

[Lupp]

See also:

aoo112122_workaroundUnwantedEffectsOfConversionToList.ods

(12.51 KiB) Downloaded 17 times

[Villeroy]

This has been created using LibreOffice. OpenOffice cannot use the same column more than once.

Surely, it can. Open your file with AOO, get the dialog for your pivot table and double click the data field. The listbox with the aggregation functions is a multi-selection listbox.

[Alex1]

The listbox with the aggregation functions is a multi-selection listbox.

Nice!

[forgottenBlock]

What kind of UI bugs?

Autocomplete to match text further up in the column is either hidden under the block cursor or just plain doesn't work.

e.g. if I have "Silver" in A1 and then in A2 I type "Sil", I get a solid black lump to the right of that suggesting that it might be "ver" when I press "Enter" but I can't tell. If the matching text is fully off the screen, I get a black blob and no clue about what LOO Calc wants to put there when I press "Enter'.

[Hagar Delest]

Don't you see that?

Autocomplete.png (13.58 KiB) Viewed 1121 times

Edit: taken with LO 24.8.2 portable on Windows 10.

[Lupp]

What's this to do with the question?

[Hagar Delest]

What's this to do with the question?

Nothing at all. But I take that the problem has been solved long ago by upgrading to LO (or through the different replies with AOO). Thus I took the liberty to ask what kind of UI glitches could still prevent to use LO.

[forgottenBlock]

Don't you see that?

No.

auofail.png (1.26 KiB) Viewed 943 times

[Hagar Delest]

Then there is an issue with maybe high contrast or a dark theme.
But that's for another topic.

[Hagar Delest]

Then there is an issue with maybe high contrast or a dark theme.
But that's for another topic.

Like this (new) one: Autoinput Suggestion 'Masked'.

By the way, thanks to add [Solved] at the beginning of the title in your first post (top of the topic) with the 🖉 button if your issue has been fixed.

[LastUnicorn]

@forgottenBlock

Read this thread again, if you haven't already done so. A 'fix' more like a temporary workaround was found in that thread: Autoinput Suggestion Masked

[Lupp]

@forgottenBlock
@Hagar Delest
@LastUnicorn
Now I'm completely confused. For me, the topic of this thread is shown as “Get max and min of filtered cells”, and quite a few posts - among them one by myself - have regarded it. Then came a post with a changed subject, and corresponding content. Now the old title is back, but the content of the younger posts no longer matches it.
Can somebody explain this to me?

[LastUnicorn]

In this thread the OP changed, or added a new topic, here: viewtopic.php?p=550132#p550132

Hagar identified that changed topic as being an issue similar to the new topic I posted here: viewtopic.php?t=112168

So, Hagar was just informing the OP of possible new information on his 'new topic' — but that new information was in another thread/topic. I posted in this thread to inform the OP of a possible solution/workaround to the changed topic he made in this thread.

Apologies, but that might be as clear as mud until anyone reads both threads. Arg!!!!! A mess!!!!!!

[Hagar Delest]

OK, let's stop here and lock the discussion. I consider that the initial question has been answered, with different methods. If someone disagrees then PM me or any other moderator and we can unlock the topic.

Note: the only title change was performed quite early by MrProgrammer because the first one given by OP was not very good, it can be seen in the first reply from MrProgrammer: Hot to use sumproduct? do i need a pivot table?
That is a standard moderator action to improve the understanding by others. It has been done cleverly after replying the initial post so that the initial title remains visible as the title of the reply (since each post in a discussion has a title).