Maxif equivalent

[Fred Smith]

I have a sumif formula, as in:

=sumif(I:I,a2,J:J)

I also want to calculate the maximum of the values which meet the criteria. Is
this possible?

 

[Don Guillett]

From an earlier post of mine today. Note NOT using on complete columns

try this array formula which must be entered using ctrl+shift+enter
=MAX(IF(A2:A22="mytruckcode",B2:B22))
or
=MAX(IF(A2:A22=c2,B2:B22))

 

[Max]

Try something like this, array-entered (press CTRL+SHIFT+ENTER):
=MAX(IF(I1:I100=A2,J1:J100))

Entire col references cannot be used

 

[Fred Smith]

Thanks guys.

I was hoping to avoid arrays, and use the entire column reference, but c'est la
vie. Perhaps in a future version of Excel.

Your recommendation works perfectly.

 

[Don Guillett]

Glad to help

 

[Max]

You're welcome, Fred.

 

[Guest]

=SUMPRODUCT(MAX((I1:I100=A2)*J1:J100))