Return Min based on 2 conditions

[John]

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.

 

[Gary''s Student]

Use a helper column:

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

Then just use:
=MIN(D2100)
or
=MAX(D2100)
or
=AVERAGE(D2100)

 

[T. Valko]

Try these array formulas** :

Min:

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

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)

 

[John]

Worked perfectly! Thanks!

Try these array formulas** :

Min:

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

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)

 

[T. Valko]

You're welcome. Thanks for the feedback!