Return Min based on 2 conditions
 

Hello,

I am trying to return the Min in one column, based on criteria in 2 other
columns. Example:

Title Region Pay
SM NJ $10
SM PA $11
SM NJ $9

I want to return the min value for the title "SM" in "NJ", which in this
small example would be $9. I will eventually need to get the Average and Max
as well.

Return Min based on 2 conditions
 

Use a helper column:

In D2 enter:
=IF(AND(A2="SM",B2="NJ"),C2,"") and copy down

Then just use:
=MIN(D2:D100)
or
=MAX(D2:D100)
or
=AVERAGE(D2:D100)

--
Gary''s Student - gsnu200774


"John" wrote:

Hello,

I am trying to return the Min in one column, based on criteria in 2 other
columns. Example:

Title Region Pay
SM NJ $10
SM PA $11
SM NJ $9

I want to return the min value for the title "SM" in "NJ", which in this
small example would be $9. I will eventually need to get the Average and Max
as well.

Return Min based on 2 conditions
 

Try these array formulas** :

Min:

=MIN(IF((A2:A4="SM")*(B2:B4="NJ")*(C2:C4<""),C2:C 4))

Max:

=MAX(IF((A2:A4="SM")*(B2:B4="NJ"),C2:C4))

Average:

=AVERAGE(IF((A2:A4="SM")*(B2:B4="NJ")*(C2:C4<""), C2:C4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"John" wrote in message
...
Hello,

I am trying to return the Min in one column, based on criteria in 2 other
columns. Example:

Title Region Pay
SM NJ $10
SM PA $11
SM NJ $9

I want to return the min value for the title "SM" in "NJ", which in this
small example would be $9. I will eventually need to get the Average and
Max
as well.

Return Min based on 2 conditions
 

Worked perfectly! Thanks!

"T. Valko" wrote:

Try these array formulas** :

Min:

=MIN(IF((A2:A4="SM")*(B2:B4="NJ")*(C2:C4<""),C2:C 4))

Max:

=MAX(IF((A2:A4="SM")*(B2:B4="NJ"),C2:C4))

Average:

=AVERAGE(IF((A2:A4="SM")*(B2:B4="NJ")*(C2:C4<""), C2:C4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"John" wrote in message
...
Hello,

I am trying to return the Min in one column, based on criteria in 2 other
columns. Example:

Title Region Pay
SM NJ $10
SM PA $11
SM NJ $9

I want to return the min value for the title "SM" in "NJ", which in this
small example would be $9. I will eventually need to get the Average and
Max
as well.

Return Min based on 2 conditions
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
Worked perfectly! Thanks!

"T. Valko" wrote:

Try these array formulas** :

Min:

=MIN(IF((A2:A4="SM")*(B2:B4="NJ")*(C2:C4<""),C2:C 4))

Max:

=MAX(IF((A2:A4="SM")*(B2:B4="NJ"),C2:C4))

Average:

=AVERAGE(IF((A2:A4="SM")*(B2:B4="NJ")*(C2:C4<""), C2:C4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"John" wrote in message
...
Hello,

I am trying to return the Min in one column, based on criteria in 2
other
columns. Example:

Title Region Pay
SM NJ $10
SM PA $11
SM NJ $9

I want to return the min value for the title "SM" in "NJ", which in
this
small example would be $9. I will eventually need to get the Average
and
Max
as well.