• 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.

Sumproduct to sum sales between dates

P

Pierre

Member
Hello,

Following a question asked in the comments section of the "Advanced sumproduct queries". I would like to sum the sales of a given person between 2 dates. I thought of using a sumproduct-based technique, but I have an issue because the length of the range of dates are not necesseraly equal...

If someone has an idea how to overcome this, or if another technique is more adequate, it'll be appreciated.
 

Attachments

  • Sum_sales.xlsx
    9.3 KB · Views: 11
Pierre

If you want to say add additional people Say John + Mary between 1 & 6 march
=SUMPRODUCT(((A2:A5=B8)+(A2:A5=C8))*(B1:Q1>=B9)*(B1:Q1<=B10)*(B2:Q5))

Where Mary is typed in C8
 
I have a question about the use of "*" vs "," in SUMPRODUCT
Especially the formula given by Somendra =SUMPRODUCT(INDEX($B$2:$Q$5,MATCH(B8,$A$2:$A$5,0),),--($B$1:$Q$1>=$B$9),--($B$1:$Q$1<=$B$10))

can also be written
=SUMPRODUCT(INDEX($B$2:$Q$5,MATCH(B8,$A$2:$A$5,0),)*($B$1:$Q$1>=$B$9)*($B$1:$Q$1<=$B$10))

Wheras other formulas, for example
=SUMPRODUCT((A2:A5=B8)*(B1:Q1>=B9)*(B1:Q1<=B10)*(B2:Q5))

cannot be changed to
=SUMPRODUCT(--(A2:A5=B8),--(B1:Q1>=B9),--(B1:Q1<=B10),(B2:Q5)) (give #VALUE)

Someone could enlighten me on what is going on in the formula?

Thanks
 
This is all explained in my Advanced Sumproduct post
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

However

Sumproduct typically relies on Multiplying the values in each Range/Array by the corresponding values in the other ranges/arrays

In the techniques used above where we are multiply 3 or more ranges together first within the sumproduct, we are effectively doing matrix manipulations first and then using Sumproduct to add up the remainders.

So in:
=SUMPRODUCT((A2:A5=B8)*(B1:Q1>=B9)*(B1:Q1<=B10)*(B2:Q5))
The internal part: (A2:A5=B8)*(B1:Q1>=B9)*(B1:Q1<=B10)*(B2:Q5)
results in a single array

where as in =SUMPRODUCT(--(A2:A5=B8),--(B1:Q1>=B9),--(B1:Q1<=B10),(B2:Q5))
Excel is simply multplying the corresponding values from each section and summing them
 
Last edited:
No * multiplies two ranges/arrays and may create a 2D matrix/array

, correct
 
You must log in or register to reply here.
Back
Top