MaxIF instead of SumIF

[Guest]

I am doing a SumIF( , , ) and that works fine. Is there a way to do a
MaxIf( , , ). I see in the help that there is no Maxif but what I want to
do is return the highest value for that particular occurance in the same data
that I am doing the SumIF.

Thank you for your help.

Steven

 

[CLR]

Hi Steven.............

You could do Data > Filter > AutoFilter............then select your group of
interest and sort decending on your column of interest..........that would
put the MAX value of that subset at the top of that column............

hth
Vaya con Dios,
Chuck, CABGx3

 

[Jason Morin]

There is no MAXIF function, but you can use an array
formula. For example, return the maximum value in A1:A100
if B1:B100 = "dog":

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

2 important notes:

1. This is an array formula, so you must press
ctrl/shift/enter, not just enter, after inserting the
formula and anytime you edit the cell. XL will place {}
around the formula.

2. Array formulas cannot handle entire columns. You
cannot use:

=MAX(IF(B:B="dog",A:A))

HTH
Jason
Atlanta, GA

 

[Guest]

Thats a good one Jason. Thanks.