• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Formula to create weekly buckets with several criteria.

I have a large list of data for sales by product code on certain days. (sample pasted below)  I need to compare a master list of codes to the sales list of codes and sum the amount sold for each product within a 7 day weekly bucket.

Code on master list matches code on sales list.
Date within 7 day bucket.
Sum each code for each week.

Item Number     Eff Date     Sales
101743           8/20/2002             20799
101744           1/29/2002             4983
101750           4/19/2002             1723
128730           10/7/2002             1357
151773           6/7/2002             2741
1529152           11/5/2002             2576
161714           4/26/2002             604
1618152           9/18/2002             262
1618153           2/11/2002             2035
162084           3/27/2002             3750
162261           1/25/2002             6026
1624151           6/12/2002             828
168578           8/13/2002             10834

I have been working on getting one single formula for this for quit a while but have not been able to derive it.

Thank you for your help.

1 Solution
If you are ok with using a pivot table to summarize the data:

Say Item Num is in column A, Eff Date is in column B and Sales in column C of sheeet Sales... header is in row 1, data starts in row 2.
Say, in sheet Master, Item Num are in column A

I would add a column 'Criteria' in column D of sheet Sales with a formula:
 D1: Criteria
 D2: = IF( ISNA(VLOOKUP($A2,Master!$A:$A,1,FALSE)), 0, 1)
ie. if product in A2 exists in Master then return 1 else 0.
Copy the formula down along the data.

Now, add a column, say E, with the year, and a column, Say F, with the week number.
Note: to be able to use the WEEKNUM() function as described bellow, you need to have the Analysis Toolpack add-in checked: menu Tools > Add-Ins, check the Analysis Toolpack.
E1: Year
E2: =YEAR(B2)
F1: Week
Copy these formulas down along the data.

Finally, create a pivot table as follow:
- Select the data (including headers)
- menu Data > Pivot Table
- Pivot table setup:
     in Column fiels: Year , Week
     in Row field: Item Num
     in Page Field: Criteria
- now, when the Pivot table displays, choose 'Criteria' equals 1 (i.e. sales product matches master product)

I hope this helps,
tab26Author Commented:
Works like a charm.  I think the whol key was having the Analysis Toolpack checked.  Thank you very much.


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now