Min with multiple criteria

[mclambchop]

Hello,

I am trying to calculate the minimum value based on three criteria. The array formula I have so far is this:

=MIN(IF((Stops!$A$7:$A$1028=A7)*(Stops!$B$7:$B$1028=B7)*(Stops!$E$7:$E$1028=C7),Stops!$P$7:$P$1028))

This works if I replace the MIN with SUM so not sure what is wrong, can anyone help please?

 

[steve the fish]

Works fine. Are you entering CSE?

 

[mclambchop]

Yes I am, changing to sum calculates fine, change it back to min, entering as an array it returns zero when the value should be 34

 

[steve the fish]

I think that means your criteria are never met in the dataset or if they are the min is indeed 0 or text.

What does this produce?

=MIN(IF(($A$7:$A$1028=A5)*($B$7:$B$1028=B5)*($E$7:$E$1028=C5),$P$7:$P$1028,1E+100))

 

[kvsrinivasamurthy]

Try this,
=SMALL(IF((Stops!$A$7:$A$1028=A7)*(Stops!$B$7:$B$1028=B7)*(Stops!$E$7:$E$1028=C7),Stops!$P$7:$P$1028),1)

 

[mclambchop]

Hi - both return zero value

 

[steve the fish]

Put a filter on your data to confirm that the result is not zero.

 

[mclambchop]

Ive done that but still the same. Interestingly I have opened the same file in Excel 2003 and it works but it wont in 2010. That doesnt sound right?

 

[steve the fish]

Im afraid im stumped. I cant think of any reason apart from the ones ive mentioned.

 

[steve the fish]

Maybe try $P$7:$P$1028*1 to convert any potential text numbers to true numbers?