IF AND statements

[Spaghetti Head]

After a bit of forum surfing I put this formula into my s/sheet =IF(AND(X4:X15="1",X4:X15="2",X4:X15="3"),"OK","ERR") with a view to the result being, that if the cell range X4 to 15 has a 1, a 2 and a 3 in it, all 3 values having to be present) then i get an OK message but if there are only 2 of the values say than I get an ERR message

i've recently started using IF and nested IF statements and think I kinda have a grasp but the above formula, in a sample dataset where all 3 values are present gives me the ERR message, and if i delete out 1 of the 3 values then i still get the error message

I'm sure i'm missing something obv but my first game of AND and i'm just not seeing it...............................

[Andrew-R]

You can't evaluate an entire range like that, try:

=IF(AND(COUNTIF(X4:X15,1)>0,COUNTIF(X4:X15,2)>0,COUNTIF(X4:X15,3)>0),"OK","ERR")

[zbor]

no, sorry, different question.

[Spaghetti Head]

Thanks Andrew

I've run that, on the first run, with the 3 needed values already in the cell range X4:X15 it returned "OK", but when i deleted 1 of the three values out it stayed as OK

[Andrew-R]

Have you got calculation set to automatic or manual? If the latter you'll need to press F9 to recalculate the formula.

[Spaghetti Head]

Thanks Andrew

That's now set to auto and the formula is working, however how do i get it to error if i have more than the 3 values i need, i iwant it to OK just on there being 1,2 & 3 in the range and to error under any other instance.....

[zbor]

Use = instead of >

=IF(AND(COUNTIF(X4:X15,1)=0,COUNTIF(X4:X15,2)=0,COUNTIF(X4:X15,3)=0),"OK","ERR")

[Spaghetti Head]

zbor, thanks for your input but that's error-ing even with the 3 values in my cell range........

[Andrew-R]

Are all of your values numeric? If so try:

=IF(AND(COUNTIF(X4:X15,1)>0,COUNTIF(X4:X15,2)>0,COUNTIF(X4:X15,3)>0,COUNTIF(X4:X15,">3")=0),"OK","ERR")

[Spaghetti Head]

Andrew

Belter! That works perfect. Now I have the formula, can you do me a vavour when you've time? I grasp logic functions quite well, can you explain the countif to me?

thanks for all your input!

[Spaghetti Head]

Also, how do I apply condition formatting to the True/False versions of the result?

[Andrew-R]

COUNTIF does pretty much what it says on the tin - it counts the number of cells in a range meeting the specified criteria.

So COUNTIF(A1:A100,3) counts all of the cells in the range A1:A100 which are equal to 3, whereas COUNTIF(A1:A100,">3") counts how many cells in that range are greater than 3 and COUNTIF(A1:A100,"<>3") counts how many cells in the range aren't equal to 3.

In your particular case, because you want the values of 1,2 and 3 in your range we know that COUNTIF(X4:X15,1) needs to be greater than zero (i.e. at least one cell in that range must contain a value of 1) and COUNTIF(X4:X15,2) also must be greater than 0 and COUNTIF(X4:X15,3) likewise. Because we don't want any other values in there COUNTIF(X4:X15,">3") must be 0. So we put all of those formula together in an AND statement and Bob's yer uncle.

To get the TRUE/FALSE version just strip it down to the AND statement...

=AND(COUNTIF(X4:X15,1)>0,COUNTIF(X4:X15,2)>0,COUNTIF(X4:X15,3)>0,COUNTIF(X4:X15,">3")=0)

[Spaghetti Head]

brilliant, thanks again for all your help1