The double minus, also known as double unary, is an operation to convert Boolean values into ones and zeroes. Simply; TRUE to one (1) and FALSE to zero (0).
The double minus operator is useful when working with arrays. The most common use case is to combine a double minus operator with SUMPRODUCT function. The SUMPRODUCT function can work with arrays without array formulas. As a result, you can bypass the limitations of Excel's conditional functions like SUMIF and SUMIFS.
Let's analyze how the double minus acts, step by step:
=SUMPRODUCT(--($B$3:$B$12=D4))
$B$3:$B$12 range refers to an array of values between B3 and B12 cells
{2018;2018;2018;2018;2018;2018;2018;2019;2019;2019}
$B$3:$B$12=D4 operation compares the values of the array with the value of the cell D4, "2019". This action returns another array which contains the Boolean values. TRUE for matched values and FALSE otherwise.
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}
The double minus converts Boolean values to ones and zeroes. So --($B$3:$B$12=D4) operation returns an array of ones and zeroes. It mean that ones represents the match and zeroes others.
{0;0;0;0;0;0;0;1;1;1}
Finally; the SUMPRODUCT sums the contents of the final array to return the number of matched values.
3
Alternatively; you can use the N function to convert Boolean values into ones and zeroes. It simply returns a value converted to a number which means TRUE to 1 and FALSE to 0.
