Array "sumif"

B

Bill Healy

Basically what I need is an arrayed "maxif", rather than
countif, or sumif

So, looking at a range of values, pick only the ones that
match the first criteria and give me the maximum value
that matches the condition

It's either a Friday afternoon and my brain is dead...or I
just can't get this to work!

Cheers in advance
 
F

Frank Kabel

Hi
if your criteria is in column A and your values in column B try the
array formula (entered with CTRL+SHIFT+ENTER)
=MAX(IF(A1:A100="your_criteria",B1:B100))

or use (non array entered)
=SUMPRODUCT(MAX((A1:A100="your_criteria")*(B1:B100)))
 
J

Jason Morin

Here's a simple example. This formula will return the max
value from column B where column A equals "apple":

=MAX(IF(A1:A100="apple",B1:B100))

Enter with ctrl/shift/enter.

HTH
Jason
Atlanta, GA
 
Top