Return Min based on 2 conditions

J

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

Gary''s Student

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)
 
T

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)
 
J

John

Worked perfectly! Thanks!

T. Valko said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top