Sum product the right formula?

[Guest]

I have the following info setup, I need to be able to sum by customer for
each month. The days represent shipping volumes for each day. This same
setup is repeated through out the spreadsheet until the end of the year. How
or can this be done?

Fri Sat. Mon Tues Wed Thur
SHIP DAY 30-Dec 31-Dec 2-Jan 3-Jan 4-Jan 5-Jan
Toyota 0 0 645
Ford 0 138 150
Honda 1440 1384 1432
Hyundia 660 620 660
Chrysler 0 284 292 288
Nissan 0 120 140 120

 

[pinmaster]

Maybe something like this:

=SUMPRODUCT((MONTH(B11)=12)*(A2:A11=E3),B211)

where E3 is the product to lookup, B11 in the ship day row, A2:A11 is
the product list and B211 is your range with number of product
shipped.

HTH
JG

 

[Guest]

That worked for the one table but I have the same table repeated for each
week of the month. This pattern is repeated until Dec 31. I need to be able
to add all quantities for each customer for each month. Thanks

Fri Sat. Mon Tues Wed Thur
SHIP DAY 30-Dec 31-Dec 2-Jan 3-Jan 4-Jan 5-Jan
Toyota 0 0 645
Ford 0 138 150
Honda 1440 1384 1432
Hyundia 660 620 660
Chrysler 0 284 292 288
Nissan 0 120 140 120
Fri Sat. Mon Tues Wed Thur
SHIP DAY 06-Jan 07-Jan 9-Jan 10-Jan 11-Jan 12-Jan
Toyota 0 0 645
Ford 0 138 150
Honda 1440 1384 1432
Hyundia 660 620 660
Chrysler 0 284 292 288
Nissan 0 120 140 120

 

[pinmaster]

To be honest, I'm not even sure it's possble, it's more a task for the
MVP's out there. So if anyone of those MVP's see's this maybe you could
help. Sorry Icouln't help you.

Regards
JG

 

[pinmaster]

Ok I've been working on this for awhile and I was able to come up wit
something, it's too much to put down in a post so I'm attaching
sample worksheet with notes.

Note: I'm assuming that your product list is in the same order all th
way down your range and there are no gaps (blank rows). Also you ma
need to alter the formulas so that they pull the data from the correc
rows. In my sample worksheet the first dates are in row 4 and produc
list starts in row 5.

Hope it's something you can use.

Regards
J

+-------------------------------------------------------------------
|Filename: month by month sales sample.zip
|Download: http://www.excelforum.com/attachment.php?postid=4181
+-------------------------------------------------------------------