Sum Product Problem
 

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


--
andyp161
------------------------------------------------------------------------
andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654
View this thread: http://www.excelforum.com/showthread...hreadid=380043

Suppose your data is in range A1:C10, column A dates, B product, C
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, and
so on till the last month you want evaluated. Format this row as mmm to
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


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=380043

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

"andyp161" wrote:


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


--
andyp161
------------------------------------------------------------------------
andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654
View this thread: http://www.excelforum.com/showthread...hreadid=380043

=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)

--
HTH

Bob Phillips

"andyp161" wrote in
message ...

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


--
andyp161
------------------------------------------------------------------------
andyp161's Profile:
http://www.excelforum.com/member.php...o&userid=11654
View this thread: http://www.excelforum.com/showthread...hreadid=380043

Mangesh's proposed formula looks good - only comment I would make is
that it will sum the month across several years if the data table
includes multiple years. To remedy this, just add on more section
identical to the Month section, but replacing 'month' with 'year' like
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 use
the '--' with conditional sumproducts - I've always just replaced the
',' with '*'. I was wondering if there is any advantage/disadvantage
of using one way or the other. Thanks, Chad


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=380043