SUMIF or SUMPRODUCT to calculate monthly sales of various products

[Harry Flashman]

Sheet1
In column C I have dates formatted as day/month/year; for example
31/01/2008 (usually there are several entries per day).
In column D I have product names (two products)
In column E I have the value of the product sales on that date.

Sheet 2
Column A I have the date formatted as mmm-yy
In Column's B and C I would like the sum of the sales of that product
for the month.

For example Sheet2

A B C
Month Shoes Bags
Jan-07 $3250 $1800
Feb-07 $1250 $4210
etc

I tried formatting the date column in Sheet1 as mmm-yy and then using
a pivot table but the pivot table saw the dates in the original format
dd/mm/yyyy (that is, not as unique months).

SUMIF is able to sum the total value of each product sales but I can't
get it to take the month into consideration as well.
I suspect that I need to use SUMPRODUCT but I am not sure how.
At some point in the future I may include more products in my summary
too (which will mean extra column in Sheet2). Alternatively it might
be better to have my products in column A and the months in columns B,
C etc.

Can anyone suggest a solution please?

 

[Pete_UK]

Put this in B2 of Sheet2:

=SUMPRODUCT((MONTH(Sheet1!$C$1:$C$1000)=MONTH($A2))*(YEAR(Sheet1!$C
$1:$C$1000)=YEAR($A2))*(Sheet1!$D$1:$D$1000=B$1),Sheet1!$E$1:$E$1000)

I've assumed you have up to 1000 rows of data on Sheet1 - adjust if
you have more. Format as you wish, then Copy into C2, then copy B2:C2
down as required. If you have another product, just put that in D1 and
copy the formula into D2 and down.

Hope this helps.

Pete

 

[Bob Phillips]

=SUMPRODUCT(--(MONTH(Sheet1!$C$2:$C$20)=MONTH(Sheet2!$A2)),
--(YEAR(Sheet1!$C$2:$C$20)=YEAR(Sheet2!$A2)),
--(Sheet1!$D$2:$D$20=B$1),Sheet1!$E$2:$E$20)

 

[Harry Flashman]

Put this in B2 of Sheet2:

=SUMPRODUCT((MONTH(Sheet1!$C$1:$C$1000)=MONTH($A2))*(YEAR(Sheet1!$C
$1:$C$1000)=YEAR($A2))*(Sheet1!$D$1:$D$1000=B$1),Sheet1!$E$1:$E$1000)

I've assumed you have up to 1000 rows of data on Sheet1 - adjust if
you have more. Format as you wish, then Copy into C2, then copy B2:C2
down as required. If you have another product, just put that in D1 and
copy the formula into D2 and down.

Hope this helps.

Pete









- Show quoted text -

Hi Pete, I think you have helped before - so once again, thank you.. I
couldn't get you formula to work at first so I alterted it slightly:
=SUMPRODUCT((MONTH(Sheet1!$C$2:$C$1000)=MONTH(Sheet2!
$A2))*(YEAR(Sheet1!$C$2:$C$1000)=YEAR(Sheet2!$A2))*(Sheet1!$D$2:$D
$1000=B$1),Sheet1!$E$2:$E$1000)

I changed the reference from C1 to C2 and then it worked fine.
I was expecting this to be an array formula because the last time I
used SUMPRODUCT for a similar request I think I had to. But not this
time. Interesting. I have much to learn.

 

[Harry Flashman]

=SUMPRODUCT(--(MONTH(Sheet1!$C$2:$C$20)=MONTH(Sheet2!$A2)),
                          --(YEAR(Sheet1!$C$2:$C$20)=YEAR(Sheet2!$A2)),
                          --(Sheet1!$D$2:$D$20=B$1),Sheet1!$E$2:$E$20)

--
__________________________________
HTH

Bob












- Show quoted text -

Bob your formula also worked for me. Thank you. Your's differred
slightly from Petes though. You used "--" instead of *.
Also you used the "-- "symbols near the start of the formula.
What does "--" mean?

 

[Pete_UK]

You're welcome - thanks for feeding back.

You can achieve the same thing using a SUM(IF( ... structure as an
array formula, and this would have to be committed using Ctrl-Shift-
Enter. SUMPRODUCT generally does not need to be.

Pete