Sum product the right formula?
 

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

Sum product the right formula?
 

Maybe something like this:

=SUMPRODUCT((MONTH(B1:D1)=12)*(A2:A11=E3),B2:D11)

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

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497552

Sum product the right formula?
 

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" wrote:


Maybe something like this:

=SUMPRODUCT((MONTH(B1:D1)=12)*(A2:A11=E3),B2:D11)

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

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497552

Sum product the right formula?
 

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
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497552

Sum product the right formula?
 

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

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

Hope it's something you can use.

Regards
JG


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

--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497552