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

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

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?
--
Regards,
Ron

DONT SHOUT!

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

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

--
HTH

Bob Phillips

"CHRIS K" wrote in message
...
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.
--
CHRISK

Use:

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

Mangesh




"CHRIS K" wrote in message
...
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.
--
CHRISK

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

And got 0 back.

So be careful with this one.

Mangesh Yadav wrote:

Use:

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

Mangesh

"CHRIS K" wrote in message
...
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.
--
CHRISK

--

Dave Peterson

The MAX works but the MIN returns 0
thanks
--
CHRISK


"Ron Coderre" wrote:

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?
--
Regards,
Ron

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


"Mangesh Yadav" wrote:

Use:

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

Mangesh




"CHRIS K" wrote in message
...
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.
--
CHRISK

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
--
CHRISK


"Bob Phillips" wrote:

DONT SHOUT!

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

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

--
HTH

Bob Phillips

"CHRIS K" wrote in message
...
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.
--
CHRISK

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)
--
Regards,
Ron


"CHRIS K" wrote:

The MAX works but the MIN returns 0
thanks
--
CHRISK


"Ron Coderre" wrote:

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?
--
Regards,
Ron

Don't forget Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"CHRIS K" wrote in message
...
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
--
CHRISK


"Bob Phillips" wrote:

DONT SHOUT!

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

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

--
HTH

Bob Phillips

"CHRIS K" wrote in message
...
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.
--
CHRISK

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

--
HTH

Bob Phillips

"CHRIS K" wrote in message
...
this works for half
what is the ctrl shift enter bit?
--
CHRISK


"Mangesh Yadav" wrote:

Use:

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

Mangesh




"CHRIS K" wrote in message
...
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.
--
CHRISK

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

array entered, Ctrl-Shift-Enter

keep pushing it :-)

--
HTH

Bob Phillips

"CHRIS K" wrote in message
...
The MAX works but the MIN returns 0
thanks
--
CHRISK


"Ron Coderre" wrote:

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?
--
Regards,
Ron