Google Sheets – Using IF(), AND(), OR() Statements

google sheets

I need help with a Google spreadsheet formula. This is what I'm trying to do:

If (A3 = X) and C3 is not any of the following choices in this list ("Ready for QA Testing on Stage","Deployed to Production","Ready for Deployment","Ready for QA Testing on Trunk") then show "Yes" otherwise show "No"

This is the formula I came up with. It doesn't return an error, but it's not filtering out the choices in the second part of the equation. I'm assuming because the <> is used for numeric and not text? Please help!!!

=If(and(A3 = "X",OR(C3<>"Ready for QA Testing on Stage",C3<>"Deployed to Production",C3<>"Ready for Deployment",C3<>"Ready for QA Testing on Trunk")),"Yes","No")

Here is a read-only link:

https://docs.google.com/spreadsheets/d/1zOt7Tn3n8yzfVPTmj9AhBOQLvJexIY-i-HIKSPb0YiQ/edit?usp=sharing

The formula is in column B. The only one in column B that should be "Yes" is B14, but as you can see B2, B6, B9 are all yes's as well.

Best Answer

It seemed simpler to put the conditions in a range (named TestR) and then use: 

=if(or(iserror(match(C3,TestR,0)),A3<>"X"),"No","Yes")

I have opted for a shorter formula, mainly by hiving off the four ‘list’ items into a named range. Rather than mix AND and OR I opted for just OR by testing for all the possibilities that should give rise to a “No” result. These are that the ColumnC value is not in the list (by trying to match it to the list and failing) as one possibility and that the ColumnA value is not “X” as the other possibility. Otherwise “Yes” should be returned.

The formula you tried has at its core a long OR function. The syntax for this is valid, the problem is with the logic. OR will return TRUE if any of its tests return true, provided none of them returns an error. You have four tests, so that part of your formula might be represented as =or(does not contain 1, does not contain 2, does not contain 3, does not contain 4).

Whichever of 1, 2, 3 and 4 (or indeed nothing or 0 or k etc) is contained, three at least of the four tests will PASS (if say it contains 4 then indeed it will not contain 3, etc). When the OR part is included in the rest of your formula it really contributes nothing, just the possibility of an error. Ignoring the error possibility, the overall result then is effectively the same as: 

=if(A3="X","Yes","No")

Adjusting to A2 from A3 (so the formula may be entered in B2 where seems first required, then copied down) it may be seen that the OP’s formula merely returns Yes if there is an X in the cell immediately to its left, and in all other cases No.

What seems to be required is “contains” for the tests, rather than “does not contain”, so some small adjustments should be sufficient: 

=If(and(A2 = "X",OR(C2="Ready for QA Testing on Stage",C2="Deployed to Production",C2="Ready for Deployment",C2="Ready for QA Testing on Trunk")),"Yes","No")
Related Topic