I need a function that works just like COUNTIF or SUMIF but is MAXIF instead.

Basically

A B
Dog 5
Dog 6
Cat 7
Bird 2
Cat 5

I want to look for Dog in Column A and get the max value associate with
those rows in Column B. I am thinking I can do this with an Array Function,
but can't seem to get my head arround it.

Here are a couple options:

This is an ARRAY FORMULA (commit with ctrl+shift+enter)
=MAX(IF(A2:A10="Dog",B2:B10))

This is a regular formula:
=MAX(INDEX((A2:A10="Dog")*B2:B10-(A2:A10<"Dog")*MIN(B2:B10),0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CindyC" wrote:

I need a function that works just like COUNTIF or SUMIF but is MAXIF instead.

Basically

A B
Dog 5
Dog 6
Cat 7
Bird 2
Cat 5

I want to look for Dog in Column A and get the max value associate with
those rows in Column B. I am thinking I can do this with an Array Function,
but can't seem to get my head arround it.

Even though CindyC got her answer, I thought I'd correct the 2nd formula in
my post....

This erroneous one:
=MAX(INDEX((A2:A10="Dog")*B2:B10-(A2:A10<"Dog")*MIN(B2:B10),0))

Should be this:
=MAX(INDEX((A2:A10="Dog")*B2:B10+(A2:A10<"Dog")*M IN(B2:B10),0))
.....(the "B10-" changed to "B10+")

Now the corrected formula behaves like the original ARRAY FORMULA I posted
and properly handles negative values in the Col_B range.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Here are a couple options:

This is an ARRAY FORMULA (commit with ctrl+shift+enter)
=MAX(IF(A2:A10="Dog",B2:B10))

This is a regular formula:
=MAX(INDEX((A2:A10="Dog")*B2:B10-(A2:A10<"Dog")*MIN(B2:B10),0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"CindyC" wrote:

I need a function that works just like COUNTIF or SUMIF but is MAXIF instead.

Basically

A B
Dog 5
Dog 6
Cat 7
Bird 2
Cat 5

I want to look for Dog in Column A and get the max value associate with
those rows in Column B. I am thinking I can do this with an Array Function,
but can't seem to get my head arround it.

Ron,

The below formula works in case column A has text value. If my col A has numerical value , how can i write the formula to find max value in Col B ???


=MAX(INDEX((A2:A10="Dog")*B2:B10+(A2:A10<"Dog")*M IN(B2:B10),0))


Thanks in advance

BK

Ron,

The below formula works in case column A has text value. If my col A has numerical value , how can i write the formula to find max value in Col B ???


=MAX(INDEX((A2:A10="Dog")*B2:B10+(A2:A10<"Dog")*M IN(B2:B10),0))


Thanks in advance

BK

Oh I figured it out.
MAX(IF(A1:A5="DOG",B1:B5))

cntl+shift+enter

Cindy


"CindyC" wrote:

I need a function that works just like COUNTIF or SUMIF but is MAXIF instead.

Basically

A B
Dog 5
Dog 6
Cat 7
Bird 2
Cat 5

I want to look for Dog in Column A and get the max value associate with
those rows in Column B. I am thinking I can do this with an Array Function,
but can't seem to get my head arround it.

=SUMPRODUCT(MAX((A1:A5="Dog")*B1:B5))

"CindyC" wrote:

I need a function that works just like COUNTIF or SUMIF but is MAXIF instead.

Basically

A B
Dog 5
Dog 6
Cat 7
Bird 2
Cat 5

I want to look for Dog in Column A and get the max value associate with
those rows in Column B. I am thinking I can do this with an Array Function,
but can't seem to get my head arround it.

I have been looking for a similar function , trying to find the Minimum
values from a list, and this one gave me #VALUE errors. It does work on a
fixed test set but not when I tried it on a test data page using equal sized
named data.

=SUMPRODUCT(MAX((namelist=C2)*monthlist)) Note: namelist is a list of
names, monthlist is date values and is the same size as name list. C2 holds
the current name being searched.

Did I miss something?

"Teethless mama" wrote in message
...
=SUMPRODUCT(MAX((A1:A5="Dog")*B1:B5))

"CindyC" wrote:

I need a function that works just like COUNTIF or SUMIF but is MAXIF
instead.

Basically

A B
Dog 5
Dog 6
Cat 7
Bird 2
Cat 5

I want to look for Dog in Column A and get the max value associate with
those rows in Column B. I am thinking I can do this with an Array
Function,
but can't seem to get my head arround it.