XOR function
XOR returns TRUE when an odd number of its conditions are TRUE. With two inputs, that means one or the other but not both. With three or more, it tests for an odd count.
Example
Read it as a sentence: check which Qty values in D2:D7 are more than 10, then return TRUE if an odd number of those checks are TRUE, which gives TRUE.
Excel shows TRUE
D
1
Qty
2
12
3
7
4
20
5
5
6
15
7
9
Items
Syntax
XOR(logical1, [logical2], ...)
Arguments in [square brackets] are optional.
-
logical1(required) - First condition.
-
logical2(optional) - More conditions.
Returns: TRUE if an odd number of conditions are TRUE.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=XOR(TRUE,FALSE) | TRUE | One true |
=XOR(TRUE,TRUE) | FALSE | Two true |
=XOR(TRUE,TRUE,TRUE) | TRUE | Three true |
=XOR(1,0) | TRUE | Numbers |
=XOR(Items!D2:D7>10) | TRUE | Array condition |
=XOR("a") | #VALUE! | Text literal |
=XOR(Items!B2:B3) | #VALUE! | Range of text |
=XOR(Data!F1) | #VALUE! | Blank only |
The Items sheet these formulas read
B
D
1
Item
Qty
2
Boxer cartridge
12
3
Briar pipe
7
4
Candle
20
5
Cigar
5
6
Clay pipe
15
7
Commonplace book
9
Items
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
Data
Watch out for
- With three or more inputs it tests for an odd count, not "exactly one".
Where it works
- Excel
- XOR arrived in Excel 2013. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .