MIN IF?

S

steven.gibbs

I have a table with two columns:

Col_1 Col_2
XXX-YYY 123
XXX-YYY 120
YYY-QQQ 143

I want to find the minimum in col_2 for each entry in Col_1.

I have done it using a pivot table but would like to be more flexible.

Is there a way of nesting the MIN and an IF statement?

Steve
 
M

Mike H

It's not quite clear what you wnat but I assume for the data below for
XXX-YYY you would want to return 120 and for YYY-QQQ you would return 143. If
that's correct try:-

=MIN(IF(A1:A4="XXX-YYY",B1:B4))

Alter the ranges to suit. It's an array so enter with Ctrl+Shift+Enter

Mike
 
D

Dave Peterson

One more if you want to avoid returning a minimum of 0 when there are no
matches.

=IF(COUNTIF(A1:A4,"xxx-yyy")=0,"No matches",MIN(IF(A1:A4="XXX-YYY",B1:B4)))

Still an array formula.
 
Top