Double negative, sometimes referred to as double unary, forces TRUE or FALSE values to their numeric equivalents, 1 or 0. Let's say that you have a list of words in a range, and you want to count how many of them contain more than 7 characters.
To find this out, we can type a LEN function:
=LEN(B4:B9)>7
For each of the five cells in the range, LEN returns a character count that is checked with >7. The result is an array of 7 TRUE or FALSE values like this.
{FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}
The 3 TRUEs are for each of the text values that qualify for the function. Now if we drop the same function under the SUMPRODUCT to find the same results, we'll get zero.
This is because TRUE and FALSE are logical, not numeric. To make Excel treat logical numbers, we need to add double negatives to convert TRUE to 1 and FALSE to 0. We're going to wrap the LEN function in the parentheses and put the double negatives in front of it like this.
Contributor : Team Leveraged Growth