Solved

# Sumproduct Confusion

Posted on 2014-03-30
187 Views
Hello

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

I am hard time understanding it

Thank you
0
Question by:Rayne

LVL 24

Accepted Solution

mankowitz earned 250 total points
ID: 39964804
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

LVL 10

Assisted Solution

broro183 earned 250 total points
ID: 39964818
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://www.teylyn.com/articles/excel-articles/sumproduct_volatile_bug/
http://www.teylyn.com/articles/excel-articles/sumproduct-error-messages/

hth
Rob
0

Author Closing Comment

ID: 39964829
Thank you All :)
0