• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Different uses for Sumproduct() (* or -- and such)

Wilco()

Wilco()

Member
I have found an exercise where sumproduct needs to be used. I noticed different ways of using the sumproduct(), but I do not understand where the * or the -- are used for. Or what they mean. I also do not know if there are other options(?) which can be used.
The aim of the exercise was to put the positive numbers in one column. And the negative in the other. Checking which product was related to what number.

The solution to the excercise:
=SUMPRODUCT(($G$5:$G$17=$J5)*($H$5:$H$17>0)*($H$5:$H$17))
=SUMPRODUCT(($G$5:$G$17=$J5)*($H$5:$H$17<0)*($H$5:$H$17))

My answer:
=SUMPRODUCT(--($G$5:$G$17=$J5);--($H$5:$H$17>0);$H$5:$H$17)
=SUMPRODUCT(--($G$5:$G$17=$J5);--($H$5:$H$17<0);$H$5:$H$17)

Can somebody explain the use of -- and * (and possible other options)?
 
An expression like ($G$5:$G$17=$J5) used in sumproduct gives u true or false - true has a value of 1 and false has a value of 0. To convert true / false to their respective values, '-' is used to make the values 0 and -1 and another '-' to make it 0 and 1.

Finally, if 1 condition is met, it gives the value 1 - if there are 2 or more conditions and the problem requires meeting all conditions, operator * is used which implies 1*1*1 giving 1 when all the conditions are met. Similarly, other mathematical operators like + (this OR that), - (NOT this), etc can be used.

There is an awesome article by Chandoo wrt the same.
 
 
Last edited:
You must log in or register to reply here.
Back
Top