Solved: the SUMPRODUCT formula

[joelle]

Hello Everyone,

It's been a while and hope you all 've been great.

I have the SUMPRODUCT formula below that works but I try to understand it ( I got it from a good-hearted exceller).
Here, the formula counts the number of "Key" occurrence only when it has an "X" from the adjacent cell. So there are only 5.

However, I dont understand the SUMPRODUCT formula in the pink cell, which is:
=SUMPRODUCT(--(C2:C16="key"),--(D2 : D16="X"))
why there are 2 dashes in front of the cell range for col C and there 2 dashes in front of the cell range for col D???

Many thanks,
Nee

[Bob Phillips]

Hello Everyone,

It's been a while and hope you all 've been great.

I have the SUMPRODUCT formula below that works but I try to understand it ( I got it from a good-hearted exceller).
Here, the formula counts the number of "Key" occurrence only when it has an "X" from the adjacent cell. So there are only 5.

However, I dont understand the SUMPRODUCT formula in the pink cell, which is:
=SUMPRODUCT(--(C2:C16="key"),--(D2 : D16="X"))
why there are 2 dashes in front of the cell range for col C and there 2 dashes in front of the cell range for col D???

Many thanks,
Nee

It is all explained at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[Jacob Hilderbrand]

The two dashes basically force the values to be numbers. Otherwise they would be True or False. So -- is like multiplying by (-1)*(-1) = 1 so the value will not change, but will be a number (0 or 1).

Alternately you could do this:

=SUMPRODUCT((C2:C16="key")*(D2 : D16="X"))

But I prefer to use --.

[joelle]

Dear XLD and Jake,
Thanks so much for the quick write-back and explanation!
Please have a great evening.

Nee