MIN IF?

[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

 

[David Biddulph]

=MIN(IF(A$1:A$3=A1,B$1:B$3)) as an *array formula*, and copy down.

 

[Guest]

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

 

[steven.gibbs]

Works a dream - Thanks!

 

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