Although I've used sumproduct for years in its "normal" application, have just been introduced to a new syntax of it as a replacement for sumifs.

As below is an example, and I understand everything except -- why the double equal characters?  What do they actually signify to the function??  It won't work without them.

=SUMPRODUCT(--(LEFT(B27:B42,3)="xxx"),D27:D42)

Thanks.

 - Mik

LEFT(B27:B42,3) | Extracts 1st Three character from B27:B42 and forms an array of string.Ex. "aaa","bbb","xxx"...

(LEFT(B27:B42,3)="xxx") | Compares the above array with string xxx.If it is xxx then it is returned TRUE,  Otherwise FALSE.So the new array is FALSE,FALSE,TRUE... for above array

(--(LEFT(B27:B42,3)="xxx") | Converts the TRUE/FALSE to 1 or 0 by double negation.It is just done to force conversion to number.You can use (LEFT(B27:B42,3)="xxx")*1 . The new array is 0,0,1...

=SUMPRODUCT(--(LEFT(B27:B42,3)="xxx"),D27:D42) | Multiplied with corresponding value of D27:D42 and sums all result.So calculation is SUM( D27 * 0 , D28*0,D29*1....)

I assumed in B27,B28,B29 the first thre char is "aaa","bbb","xxx" and tried to explain with the example.

Hope it helps.

Hi,

As far as I know, the first minus sign means the cell date changes type.

The first minus sign
-(true)=-1
-(false)=0
The second minus sign
--(true)=1
--(false)=0

It makes the SUMPRODUCT to handle logic type array.

If you need to count the cellA1 to cellD1 data>3.

For example:

=COUNTIF(A1:D1,">3")

=SUMPRODUCT(--(A1:D1>3))

Them will be the same result.

George Zhao
TechNet Community Support