Array "sumif"

[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

 

[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)))

 

[Don Guillett]

=MAX(IF(H1:H10=condition,I1:I10))

 

[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