Dashes in Formulas "--"

It's called a double unary minus. Minus minus is the same as plus. It coerces a TRUE/FALSE boolean into 1/0. The same could be achieved by multiplying by 1 or adding zero. But -- is in vogue at the moment because apparently it is a nanosecond or two quicker than the alternatives.

So this formula:

=SUMPRODUCT(--($B$2:$B$8=F2),--($D$2:$D$8="X"))

could be written:

=SUMPRODUCT(1*($B$2:$B$8=F2),1*($D$2:$D$8="X"))

I don't understand how this works.

It would more likely be written:

=SUMPRODUCT(($B$2:$B$8=F2)*($D$2:$D$8="X"))

It compares each cell in B2:B8 with F2 and returns an array of TRUE/FALSE values. Then it compares each cell in D2:D8 with "X" and returns another (equally sized) array of TRUE/FALSE values. The 2 arrays are multiplied together, and in the process TRUE is coerced to 1 and FALSE to zero. That results in an array of 1/0 values (1*1=1, 1*0=0, 0*1=0, 0*0=0). This array is then summed. The effect is a count of the two conditions being TRUE.

THANK YOU!

I notice that the formula with -- is NOT an array. Does the one with the * have to be entered as an array?

In the -- example there are two arrays separated by a comma, ie SUMPRODUCT has two arguments. Each array is converted to 1/0 by -- and the 2 arrays are multiplied. In the second example there is only one argument, which comes about by multiplying the arrays together. It's a subtle difference, but if the formula were:

=SUMPRODUCT(($B$2:$B$8=F2),($D$2:$D$8="X"))

the SUMPRODUCT function per se would not coerce the booleans to values when multiplying its 2 arguments. The coercion has to done for it, either by -- or by multiplication (the latter being a feature of Excel's calculation engine).