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

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...

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.

Originally Posted by rmellison

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...

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.

"Bryan Hessey" wrote:

>
> 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.
>
>
> rmellison Wrote:
> > 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
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=489753
>
>

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)



rmellison wrote:
>
> 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...

--

Dave Peterson

Easy! Not a VLOOKUP in sight!

Many thanks!

"Dave Peterson" wrote:

> 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)
>
>
>
> rmellison wrote:
> >
> > 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...
>
> --
>
> Dave Peterson
>