Small function help for 2 criteria

[USCguy09]

I'm trying to pull the smallest value (besides 0) from array B1:B10 in relation to array A1:A10 but the value must NOT include data from USA and JAPAN.

So basically, looking at the photo below, I want the smallest value from
the data points B3,B4,B5,B8,B9,B10. The smallest value should be 3.

The equation would go into say cell E1. I figured out how to find the small for a specific criteria but not for excluding two criteria. Any help would be appreciated.

 

[Haseeb Avarakkan]

Hello, Welcome to MrExcel.

Try this Array Formula,

=MIN(IF(A1:A10<>"USA",IF(A1:A10<>"JAPAN",IF(1-(B1:B10=0),B1:B10))))

 

[Trouttrap2]

Hello,
here is another alternative
{=MIN(IF(A1:A6<>"USA",IF(A1:A6<>"Japan",B1:B6)))}

 

[Trouttrap2]

I guess if you wanted to use the SMALL formula, you could do this:
{=SMALL(IF(A1:A6<>"USA",IF(A1:A6<>"JAPAN",B1:B6)),1)}

 

[T. Valko]

I'm trying to pull the smallest value (besides 0) from array B1:B10 in relation to array A1:A10 but the value must NOT include data from USA and JAPAN.

So basically, looking at the photo below, I want the smallest value from
the data points B3,B4,B5,B8,B9,B10. The smallest value should be 3.

The equation would go into say cell E1. I figured out how to find the small for a specific criteria but not for excluding two criteria. Any help would be appreciated.

Here's another one...

Use cells to hold the criteria:

• D1 = USA
• E1 = JAPAN

I'm assuming there won't be any negative numbers as is shown in your sample data.

Array entered**:

=MIN(IF(ISNA(MATCH(A1:A10,D1:E1,0)),IF(B1:B10>0,B1:B10)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[T. Valko]

=MIN(IF(A1:A10<>"USA",IF(A1:A10<>"JAPAN",IF(1-(B1:B10=0),B1:B10))))

Why not simply:

B1:B10<>0

Or:

B1:B10>0

1-(B1:B10=0)

That seems counterintuitive to me.

 

[Haseeb Avarakkan]

Why not simply:

B1:B10<>0

Or:

B1:B10>0

1-(B1:B10=0)

That seems counterintuitive to me.

Thank you, Biff

'1-(B1:B10=0)' initially this was comes to my mind. Didn't think about a simple 'B1:B10<>0'

 

[USCguy09]

thanks for all the replies!
i'll try some of these and will reply back tomm!

 

[USCguy09]

SOLVED!

Thanks guys!