Sample data below, spreadsheet attached
I have a spreadsheet data that has a list of values by date range. These values indicate the delivery number for a particular order/model combination over a given date range. For example:
Order#1: Between 1/1/2010 and 1/15/2010 there were 4,000 deliveries made for Product A.
Order #2: Between 1/8/2010 and 1/13/2010 there were 1,200 deliveries made for Product B.
Order # 3: Between 1/10/2010 and 1/12/2010 there were 445 deliveries made for Product A.
I want to be able to find the total delivery per work day of orders by product, but add the delivery together for instances where the dates adn the products overlap to get a Total Delivery per Day over all orders by product.
Belive me, your help is very appreciated.
(copy/paste to excel)
Order# Product Model Start Date End Date Number of Workdays between Start and End Deliveies Made Deliveries Per day
848044 Product B 1/11/2010 4/6/2010 62 8,446 136
848044 Product B 1/11/2010 4/6/2010 62 845 14
848044 Product B 1/11/2010 4/6/2010 62 1,173 19
848044 Product B 1/11/2010 4/6/2010 62 1,173 19
848044 Product B 1/11/2010 4/6/2010 62 845 14
845045 Product B 1/11/2010 3/5/2010 40 4,000 100
845045 Product B 1/11/2010 3/5/2010 40 1,400 35
845045 Product B 1/11/2010 3/5/2010 40 3,000 75
838559 Product B 1/11/2010 3/19/2010 50 6,000 120
801892 Product B 1/11/2010 3/12/2010 45 13,050 290
801892 Product B 1/11/2010 3/12/2010 45 5,175 115
801892 Product B 1/11/2010 3/12/2010 45 9,005 200
801892 Product A 1/11/2010 3/12/2010 45 29,250 650
801892 Product A 1/11/2010 3/12/2010 45 2,925 65
801892 Product A 1/11/2010 3/12/2010 45 20,183 449
832243 Product B 1/11/2010 3/30/2010 57 10,400 182
832243 Product B 1/11/2010 3/30/2010 57 4,400 77
788741 Product B 1/11/2010 3/26/2010 55 3,920 71
788741 Product B 1/11/2010 3/26/2010 55 3,724 68
829309 Product B 1/11/2010 4/9/2010 65 35,750 550
829309 Product B 1/11/2010 4/9/2010 65 13,000 200
829309 Product B 1/11/2010 4/9/2010 65 25,025 385
846635 Product B 1/11/2010 3/15/2010 46 9,200 200
846635 Product B 1/11/2010 3/15/2010 46 1,610 35
846635 Product B 1/11/2010 3/15/2010 46 6,348 138
846635 Product A 1/11/2010 3/15/2010 46 2,530 55
846635 Product A 1/11/2010 3/15/2010 46 1,746 38
856092 Product B 1/11/2010 3/12/2010 45 30,375 675
856092 Product B 1/11/2010 3/12/2010 45 5,625 125
856092 Product B 1/11/2010 3/12/2010 45 18,225 405
856093 Product B 1/11/2010 3/12/2010 45 27,000 600
856093 Product B 1/11/2010 3/12/2010 45 4,500 100
856093 Product B 1/11/2010 3/12/2010 45 16,200 360
856093 Product A 1/11/2010 3/12/2010 45 2,250 50
856093 Product A 1/11/2010 3/12/2010 45 4,725 105
812632 Product B 1/11/2010 3/12/2010 45 13,500 300
812632 Product B 1/11/2010 3/12/2010 45 9,000 200
812632 Product B 1/11/2010 3/12/2010 45 8,775 195
812632 Product A 1/11/2010 3/12/2010 45 5,850 130
812632 Product A 1/11/2010 3/12/2010 45 1,800 40
Bookmarks