HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA)?

C

CHRIS K

HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA) &
HOW DO I GET THE EQUIVALENT OF MINIF(RANGE,CRITERIA)?
i've tried loads of rubbish but cant get the answer i want.
 
R

Ron Coderre

Here's one way:

In my example, I put letters down Col A and Values down Col B:
=SUMPRODUCT(MAX((A2:A100="a")*(B2:B100)))

You could also engage an autofilter and use the SUBTOTAL function to return
the maximum visible value: =SUBTOTAL(4,range)

Does that help?
 
B

Bob Phillips

DONT SHOUT!

=MAX(IF(A1:A100<>"value",B1:B100)

which is an array formula, so commit with Ctrl-Shift-Enter.
 
M

Mangesh Yadav

Use:

=MAX(A1:A4*(B1:B4="a"))
confirm with control - shift - enter
And similar for min

Mangesh
 
D

Dave Peterson

I put this in A1:B4
-1 a
-2 b
-3 a
-4 a

And got 0 back.

So be careful with this one.
 
C

CHRIS K

sorry for the caps bob

cant get this one to work, was looking at the wrong one when i pressed the
'yes' button.
this gives me 0 for everything
Shall try harder to sort it
thanks
 
R

Ron Coderre

For MIN, try this:

=SUMPRODUCT(MIN(($A$2:$A$100="a")*($B$2:$B$100)+($A$2:$A$100<>"a")*10^10))

(The additions to the formula cause non-matches to equate to 100,000,000,000
instead of zero)
 
Top