Finding the maximum of a subset of values on a different sheet

[Guest]

I have a list of parameter names and a corresponding list of values; there
may be ten or so of each of the parameters within the list. I have been using
SUBTOTAL(4,range) to find the maximum of a parameter when filtered using
autofilter, and up until now this has been fine.

Now I find myself needing to do a similar thing for an unfiltered list which
is on another worksheet. I want to be able to find the maximum of all the
values corresponding to all entries of a particular parameter within a list.
I suspect that the way to do it is using VLOOKUP or INDEX/MATCH to find an
array of the numbers I want, and then finding the maximum of that array, but
so far I haven't cracked it....

Any suggestions gladly appreciated...

 

[Bryan Hessey]

If you were using Subtotal, then the Sumproduct would provide the
answer, as in D1 enter:

=SUMPRODUCT(($A$1:A$99=C1)*(B$1:B$99))

which would provide the total of the item in C1, and when
formula-dragged down to cover all items entered in column C will total
each item for you.

Adjust the A1:A99 and B1:B99 to your data.

If you were after the 'Maximum' value from the range please let me
know.

 

[Guest]

I am after the maximum.

I had been using SUBTOTAL(4,RANGE), where 4 in the formula is option 4 that
signifies that I want to return the maximum from the range. My list was
filtered to show all entries for only one parameter. I was reliably informed
on this NG that SUBTOTAL function only works on visible values in a filtered
list.

My predicament now is that I need to do the same (find the max of a given
parameter) from a different sheet, without having the optionto filter the
data. I'm not sure if I can use SUMPRODUCT for this....

Thanks for your assistance.

 

[Dave Peterson]

One way:

=MAX(IF(Sheet2!A1:A10="hi",Sheet2!B1:B10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(Sheet2!A1:A10,"hi")=0,"No matches",
MAX(IF(Sheet2!A1:A10="hi",Sheet2!B1:B10)))

(one cell, and still an array formula)

 

[Guest]

Easy! Not a VLOOKUP in sight!

Many thanks!

One way:

=MAX(IF(Sheet2!A1:A10="hi",Sheet2!B1:B10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

You could even check to see if there was any data that matched:

=IF(COUNTIF(Sheet2!A1:A10,"hi")=0,"No matches",
MAX(IF(Sheet2!A1:A10="hi",Sheet2!B1:B10)))

(one cell, and still an array formula)