Sum Product Problem

[andyp161]

Hope you can help!!

Column A=dates eg 17/06/05 etc
Column B=Product eg A, B, C, D, E etc
Column C=Quantity eg 1, 2, 3, 4, 5 etc

Using SUMPRODUCT I am trying to calculate the quantity of different
product types that are sold in each month of the year. My problem is
that column a contains actual dates so for June for example, I need to
calculate:
how many rows in column A contain a date between 01/06/05 and 30/06/05

how many rows in column B that meet the above contain product A,
product B, product C etc
the sum of the values (quantities sold) that meet the above.

Therefore, I want to create a table that would look something like the
below:

Product Sales p/m
Type Jun Jul Aug
A 20 28 45
B 40 56 36
C 35 35 59

 

[mangesh_yadav]

Suppose your data is in range A1:C10, column A dates, B product,
quantity. Do the following.

Lets start from cell G1. Enter the first date's month's 1 day. For e.g
you are starting from jun, enter 1-jun-05, then H1 enter 1-jul-05, an
so on till the last month you want evaluated. Format this row as mmm t
display only the months.

cells F2, F3, F4 should have a, b, c... your products.

In G2, enter the formula:
=SUMPRODUCT(--(MONTH($A$1:$A$10)=MONTH(G$1)),--($B$1:$B$10=$F2),$C$1:$C$10)

and copy across the length and breadth of the table


Manges

 

[Guest]

try something like
i=sumproduct(--(month(input date range in column A)=(cell in output date
range)),--(input Product range in column B=(Cell in output product
range),Quantity range in Column C)
note you cannot use entire columns in Sumproduct A1:A60000 is ok A:A is not
also the arrays in each criteria must be the same size

 

[Bob Phillips]

=SUMPRODUCT(--(MONTH(A2:A100)=6,)--(B2:B100="B"),C2:C100)

etc.

Best to put the list of products in a cell and reference that so as to make
easy copying

=SUMPRODUCT(--(MONTH($A$2:$A$100)=6,)--($B$2:$B$100=M1),$C$2:$C$100)

 

[cvolkert]

Mangesh's proposed formula looks good - only comment I would make i
that it will sum the month across several years if the data tabl
includes multiple years. To remedy this, just add on more sectio
identical to the Month section, but replacing 'month' with 'year' lik
follows:

=SUMPRODUCT(--(MONTH($A$1:$A$10)=MONTH(G$1)),--(YEAR($A$1:$A$10)=YEAR(G$1)),--($B$1:$B$10=$F2),$C$1:$C$10)

Also, one question for my benefit - I've noticed that some people us
the '--' with conditional sumproducts - I've always just replaced th
',' with '*'. I was wondering if there is any advantage/disadvantag
of using one way or the other. Thanks, Cha