Help with zeros and blanks in a MIN or SMALL function

[mcbrown]

I am trying to write a MIN or SMALL function that finds the smalles non-zero number in Column B that matches a criteria in Column A. I don't get the right result at times. For example, when I use the formula and data below I get "problem" when I should get 2. However, it seems to work if B2 is a number greater than zero. If B2 is zero, it also returns "problem".

{=IF(MIN(IF((A1:A7=5),B1:B7))>0,MIN(IF((A1:A7=5),B1:B7)),"problem")}

Column A Column B
Row 1 5 8
Row 2 5
Row 3 3 9
Row 4 3 3
Row 5 5 2
Row 6 4 1
Row 7 1 0

Can you help me?

Thanks!,

Mark

 

[Aladin Akyurek]

I am trying to write a MIN or SMALL function that finds the smalles non-zero number in Column B that matches a criteria in Column A. I don't get the right result at times. For example, when I use the formula and data below I get "problem" when I should get 2. However, it seems to work if B2 is a number greater than zero. If B2 is zero, it also returns "problem".

{=IF(MIN(IF((A1:A7=5),B1:B7))>0,MIN(IF((A1:A7=5),B1:B7)),"problem")}

Column A Column B
Row 1 5 8
Row 2 5
Row 3 3 9
Row 4 3 3
Row 5 5 2
Row 6 4 1
Row 7 1 0

Can you help me?

Thanks!,

Mark

D2: 5

Control+shift+enter, not just enter:

=MIN(IF(A2:A8>=D2,IF(B2:B8>0,B2:B8)))

should succeed.

If you want to have some control...

=IF(COUNTIF(A2:A8,D2),MIN(IF(A2:A8>=D2,IF(B2:B8>0,B2:B8))),"Not Available")

 

[mcbrown]

Thank you very much Aladin. Your correction works like a charm!

Best,

Mark

 

[Aladin Akyurek]

Thank you very much Aladin. Your correction works like a charm!

Best,

Mark

You are welcome. Thanks for providing feedback.