Summary
To check two items with exclusive OR (one or the other, but not both), you can use the XOR function. In the example shown, E5 contains the following formula:
=XOR(C5="x",D5="x")
This formula returns TRUE when either coffee or tea has an "x". It returns FALSE if both coffee and tea have an "x", or if neither coffee nor team has an "x".
Generic formula
=XOR(criteria1,criteria2)
Explanation
In this example, the XOR function contains two expressions, one to test for an "x" in column C, and one to test for an "x" in column D.
C5="x" // TRUE if coffee is "x"
D5="x" // TRUE if tea is "x"
With two logical criteria, XOR has a particular behavior, summarized in the table below:
| Coffee | Tea | Result |
|---|---|---|
| TRUE | FALSE | TRUE |
| FALSE | TRUE | TRUE |
| TRUE | TRUE | FALSE |
| FALSE | FALSE | FALSE |
At each row in column E, XOR evaluates values in columns C and D and returns a TRUE or FALSE result. This behavior is sometimes referred to as "exclusive OR", meaning only one result can be true.
Note: with more than two criteria, XOR behavior changes, as explained on the XOR function page.
