Unlike the **OR function **which returns TRUE if only 1 condition is TRUE, the XOR function returns TRUE if the number of TRUE conditions is **ODD** and FALSE if the number of TRUE Conditions is **EVEN**.

Like the OR Function, at least one of the test conditions must evaluate to TRUE to return TRUE. XOR function will return FALSE if all conditions evaluate to FALSE

**=OR( 2=2, 2>3, 4>3 )=TRUE** this is because at least 1 condition is TRUE

**=XOR( 2=2, 2>3, 4>3 )=FALSE** this is because the number of TRUE Conditions is EVEN

If no logical conditions are found, XOR returns #VALUE error

Interestingly, the XOR function can be represented by the minus (-) Sign

(**2=2) – (2>3) – (4>3**) => FALSE

(**2>2) – (2>3) – (4>3**) => TRUE

How is this possible?

In Excel, the numeric equivalent of a FALSE Boolean value is Zero (0) while any other number (positive or negative) is equivalent to a TRUE Boolean Value.

(**2=2) – (2>3) – (4>3**) => TRUE – FALSE – TRUE ==> 1- 0 -1 ===> 0 ( FALSE )

(**2>2) – (2>3) – (4>3**) => FALSE – FALSE – TRUE ==> 0- 0 -1 ===> -1 (TRUE )

NB: One major difference between the minus (-) symbol and the XOR function is that the XOR function is not able to Spill logically!

Table of Contents

**FILTER Function Using XOR logic**

Since the FILTER function has a spill capability, we shall use it to show the difference between the minus (- ) sign and the XOR function

**=FILTER(Supplier[Name],XOR(ISNUMBER(Supplier[1st pay]),ISNUMBER(Supplier[2nd pay])) )** ….Since XOR does not spill then the function result to CALC# error

**=FILTER(Supplier[Name],ISNUMBER(Supplier[1st pay])-ISNUMBER(Supplier[2nd pay]))**…this works since the minus sign is able to spill

**IF Function Using XOR logic**

If you are checking single records instead of arrays, both XOR and Minus (-) sign works the same

**NOTE:**

XOR function can be used to test for Empty and non-empty cells

=IF(XOR(Supplier3[@[1st pay]:[2nd pay]]), TRUE, FALSE)

Above function will return:

- #VALUE error if all cells are empty
- TRUE if the number of non-empty cells are ODD
- FALSE if the number of non-empty cells are EVEN

## COUNTIF Function **Using XOR logic**

COUNTIF function does not allow array calculations but there is a walk-around

Count all the suppliers paid only 1 time

=SUM( ABS( ISNUMBER(pays[1st pay])-ISNUMBER(pays[2nd pay] ) ) )

## SUMIF Function **Using XOR logic**

Just like the COUNTIF function, SUMIF does not allow array calculations but there is a walk-around

=SUM( ABS(ISNUMBER(tblpay[1st pay])-ISNUMBER(tblpay[2nd pay])) * tblpay[Sales] )

Good stuff. But 1 question:

where there are 2 TRUE & 1 FALSE results, = 1+1+0=2, i.e., TRUE (since result is not 0).

But you said that if TRUES are even, then XOR returns FALSE.

Please resolve this.

Good stuff. But 1 question:

where there are 2 TRUE & 1 FALSE results, = 1+1+0=2, i.e., TRUE (since result is not 0).

But you said that if TRUES are even, then XOR returns FALSE.

Please resolve this.

Hi Sandeep,

XOR logic uses the minus (-) sign not the Plus sign (+)

Therefore =1-1-0 = 0

EVEN true values = false

ODD true values = true