Sumproduct Confusion

Hello

Can anyone explain what is that -- within the sumproduct?
SUM(--(myExpensesItems="Sugar"))

I am hard time understanding it


Thank you
RayneAsked:
Who is Participating?
 
mankowitzConnect With a Mentor Commented:
the double minus sign has the effect of forcing a value to be an int, so "true" becomes 1 and "false" becomes zero. This is very useful in sumproduct calculations because you are often multiplying one range against another, so you can use the 0 and 1's to include certain values of the range.

See http://www.k2e.com/tech-update/tips/143-using-two-minus-signs-in-excel
0
 
broro183Connect With a Mentor Commented:
hi,

The "--" is called the double unary operator (aka double negative or double minus) & the first negative sign converts arrays from a True/False to a -1/0 while the second negative sign converts the -1/0 to 1/0 within the sumproduct formula.

Here are some other explanations, tips and caveats:
http://mcgimpsey.com/excel/formulae/doubleneg.html
http://xldynamic.com/source/xld.SUMPRODUCT.html
http://windowssecrets.com/forums/showthread.php/109039-sumproduct-explained-with-double-unary-operator-(2003)
http://www.teylyn.com/articles/excel-articles/sumproduct_volatile_bug/
http://www.teylyn.com/articles/excel-articles/sumproduct-error-messages/

hth
Rob
0
 
RayneAuthor Commented:
Thank you All :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.