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

[Guest]

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.

 

[Guest]

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?

 

[Bob Phillips]

DONT SHOUT!

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

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

 

[Mangesh Yadav]

Use:

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

Mangesh

 

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

 

[Guest]

The MAX works but the MIN returns 0
thanks

 

[Guest]

this works for half
what is the ctrl shift enter bit?

 

[Guest]

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

 

[Guest]

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)

 

[Bob Phillips]

Don't forget Ctrl-Shift-Enter.

 

[Bob Phillips]

makes it an array formula, otherwise it doesn't work on the whole range.

 

[Bob Phillips]

=MIN(IF(A2:A100="a",B2:B100))

array entered, Ctrl-Shift-Enter

keep pushing it