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 column 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.
Related formulas
Count cells equal to this or that
In this example, the goal is to count cells in the range D5:D15 that contain "red" or "blue". For convenience, the D5:D15 is named color . Counting cells equal to this OR that is more complicated than it first appears because there is no built-in function for counting with OR logic. The COUNTIFS...
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...
Related functions
XOR Function
The XOR function performs what is called "exclusive OR". With two logical statements, XOR returns TRUE if either statement is TRUE, but returns FALSE if both statements are TRUE. If neither is TRUE, XOR also returns FALSE.
