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?

 

[mclambchop]

Doesnt make any difference, there are only two numbers anyway, I am just using a small test area of data before I apply to my large recordset. If it works in 2003 then that will have to do, thank you so much for your time, it is appreciated

 

[Aladin Akyurek]

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?

Control+shift+enter:

=IFERROR(SMALL(IF(Stops!$A$7:$A$1028=A7,
  IF(Stops!$B$7:$B$1028=B7,IF(Stops!$E$7:$E$1028=C7,
  Stops!$P$7:$P$1028))),1),"No data")

What does this yield?

 

[pgc01]

Hi

Could it be that you have cases where all 3 conditions are met but the corresponding cell in column P is empty or with a null string?

Try your formula making sure you only use number values:

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