Min if and

[Johnny Image]

My formula isn't working and I need some help. I'm trying to return the minimum value of one column based on the value of another. Ihave two sheets in my workbook: Data and Tables. The formula is in Tables

{=MIN(IF(AND(Data!$O$2:$O$6141="yes",Data!$R$2:$R$6141=J3),Data!$N$2:$N$6141))}

For Example

Sheet: Data

ColumnO ColumnN ColumnR
Yes 100 Red
No 50 Blue
Yes 120 Red

Sheet: Tables
J3 = Red

It should return 100, but instead I get nothing. WHat am I doing wrong?

--John

[Pete_UK]

Try it like this:

{=MIN(IF((Data!$O$2:$O$6141="yes")*(Data!$R$2:$R$6141=J3),Data!$N$2:$N$6141))}

You can't use AND like that to work on arrays, but * is equivalent. Obviously, don't type the { } - use CSE to commit.

Hope this helps.

Pete

[Johnny Image]

Try it like this:

{=MIN(IF((Data!$O$2:$O$6141="yes")*(Data!$R$2:$R$6141=J3),Data!$N$2:$N$6141))}

You can't use AND like that to work on arrays, but * is equivalent. Obviously, don't type the { } - use CSE to commit.

Hope this helps.

Pete

That worked!! Thanks for the help and the lesson. I don't use arrays often enough to know the in and outs.

--John