A unary operation is an operation with only one operand (input). The double unary (also called a double negative) is an operation used to coerce TRUE FALSE values to ones and zeros in more advanced formulas, especially formulas that work with arrays.
For example, the screen above shows two groups that contain five values each. Three values match and two are different. The formula used to count matching values is:
=SUMPRODUCT(--(B5:B9=C5:C9))
Working from the inside-out, the B5:B9 is compared to C5:C9 in a simple expression that creates an array of five TRUE FALSE values:
=SUMPRODUCT(--({TRUE;TRUE;FALSE;TRUE;FALSE}))
We want to count matches (TRUE values) but the SUMPRODUCT function will ignore non-numeric values, so we use a double unary to change the TRUE FALSE values to ones and zeros.
This works because Excel automatically coerces TRUE/FALSE to 1/0 during math operations. The first negative changes TRUE values to -1, and FALSE values to 0. The second negative simply changes -1 to 1. The result inside SUMPRODUCT looks like this:
=SUMPRODUCT({1;1;0;1;0})
With only one array to work with, SUMPRODUCT simply returns the sum of all items, 3.
