?
Solved

Sumproduct Confusion

Posted on 2014-03-30
3
Medium Priority
?
205 Views
Last Modified: 2014-03-30
Hello

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

I am hard time understanding it


Thank you
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 1000 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

by:broro183
broro183 earned 1000 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://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
 

Author Closing Comment

by:Rayne
ID: 39964829
Thank you All :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question