If cell is x or y and z

The goal is to identify records where the color is "Red" or "Green" and the quantity is greater than 10. If a row meets all conditions, the formula should return "x". If any condition is not true, the formula should return an empty string (""). This problem can be solved with the IF function together with the OR function and the AND function.

IF function

The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. For example, if cell A1 contains the text "Red", then:

=IF(A1="red",TRUE) // returns TRUE
=IF(A1="blue",TRUE) // returns FALSE

Notice the IF function is not case-sensitive. Also, notice that IF automatically returns FALSE even though no value is provided for a false result.

OR function

The OR function returns TRUE if any argument is TRUE. For example, if cell A1 contains "Red" then:

=OR(A1="Red",A1="Green") // returns TRUE
=OR(A1="Blue",A1="Green") // returns FALSE

AND function

The AND function returns TRUE if all arguments are TRUE. For example, if cell A1 contains "Red" and B1 contains 10, then:

=AND(A1="Red",B1=10) returns TRUE
=AND(A1="Red",B1=12) returns FALSE
=AND(A1="Blue",B1=10) returns FALSE

Putting it all together

The goal is to identify records where the color is "Red" or "Green" and the quantity is greater than 10. The formula in cell E5 is:

=IF(AND(OR(B5="red",B5="green"),C5>10),"x","")

Note that the OR function appears inside the AND function. This means the OR function must return TRUE in order for the AND function to return TRUE. In other words, the color must be "Red" or "Green" and the quantity must be greater than 10. In cell E5, the formula evaluates like this:

=IF(AND(OR(B5="red",B5="green"),C5>10),"x","")
=IF(AND(OR(TRUE,FALSE),C5>10),"x","")
=IF(AND(TRUE,TRUE),"x","")
=IF(TRUE,"x","")
="x"

Notice the OR function is evaluated first because it is nested inside the AND function. In other words, the OR function must return a result before the AND function can return a result. In the same way, both the OR function and the AND function must return a result before the IF function can return a result. In the end, the IF function returns "x", because the AND function returns TRUE. In cell E6, the formula evaluates like this:

=IF(AND(OR(B6="red",B6="green"),C6>10),"x","")
=IF(AND(OR(FALSE,FALSE),C6>10),"x","")
=IF(AND(FALSE,FALSE),"x","")
=IF(FALSE,"x","")
=""

The result is an empty string (""), because the color is not "Red" or "Green" and the quantity is not greater than 10. Even if the quantity were greater than 10, the result would be the same because the color would not be "Red" or "Green".  

Note: if we didn't supply an empty string ("") for the value_if_false argument, the formula would return FALSE when the logical test returned FALSE.