Formula to create weekly buckets with several criteria.

Posted on 2003-03-13
Medium Priority
Last Modified: 2008-03-03
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.

Question by:tab26
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 16

Accepted Solution

sebastienm earned 300 total points
ID: 8129370
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,

Author Comment

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


Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question