multiple criteria SUMPRODUCT

[matt4003]

Hello Everyone,

I am sure this has been discussed, but I can't seem to find a threa
that matches my exact situation.

I have one sheet called "Actuals" and one called "JuneActuals". Th
"Actuals" sheet contains Product and Months where the total units sol
for the month would be listed by product:
A B(Jan04) C(Feb04) etc.
ProductA
ProductB

The second sheet "JuneActuals" contains the raw product shipment data.
In column A the product is listed each time the product ships, column
has the date it shipped, and column C has the quantity shipped.

What I want to do is write a formula that Sums the quantity of Product
for all shipments that happened in Jan, then in Feb, then in March
etc. But there can be several shipments of ProductA in one month
etc.

Anyone have any ideas??

Thanks in advance!
Mat

 

[Domenic]

Hi Matt,

Following your example, put this formula in B2 of your Actuals Sheet,
copy across as far as you need to, and down as far as you need to:

=SUMPRODUCT((MONTH(JuneActuals!$B$1:$B$3)=MONTH(Actuals!B$1))*(YEAR(JuneA
ctuals!$B$1:$B$3)=YEAR(Actuals!B$1))*(JuneActuals!$A$1:$A$3=Actuals!$A2)*
(JuneActuals!$C$1:$C$3))

Adjust the ranges for your JuneActuals sheet to suit your needs.

Hope this helps!