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

# 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.

Criteria:
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.

0
tab26
1 Solution

Commented:
tab26,
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
F2: =WEEKNUM(B2)
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,
Sebastien
0

Author Commented:
Works like a charm.  I think the whol key was having the Analysis Toolpack checked.  Thank you very much.

Terry
0

## Featured Post

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