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, 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

[cvolkert]

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

[bj]

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

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

--
HTH

Bob Phillips

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[bj]

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

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

--
HTH

Bob Phillips

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[bj]

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

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

--
HTH

Bob Phillips

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[bj]

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

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

--
HTH

Bob Phillips

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[bj]

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

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

--
HTH

Bob Phillips

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[bj]

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

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

--
HTH

Bob Phillips

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[bj]

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

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

--
HTH

Bob Phillips

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

--
HTH

Bob Phillips

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[bj]

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

[bj]

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

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

--
HTH

Bob Phillips

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[bj]

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

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

--
HTH

Bob Phillips

"andyp161" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>