chuckles1066
Banned
- Joined
- Dec 20, 2004
- Messages
- 372
Afternoon all, I hope someone can help.
I need to produce some monthly sales stats broken down by week. For week one of the month I've been using something along the lines of
=SUMPRODUCT(--(A1:A9999="WALMART"),--(B1:B9999="NEWSPAPERS"),E1:E9999).
Week two of the month sees the last bit change from E1:E9999 to F1:F9999, week three to G1:G9999 and so on.
What this means is each month I am going to have to hack the formula about to reflect where the current data for each week lives; each new week sees a new column of data.
Is there a way to force Excel to automatically look at the correct column depending on which week/month it is? My limited knowledge tells me INDEX or MATCH will come into it at some point but I haven't got a clue how to code a formula that works.
All advice appreciated.
I need to produce some monthly sales stats broken down by week. For week one of the month I've been using something along the lines of
=SUMPRODUCT(--(A1:A9999="WALMART"),--(B1:B9999="NEWSPAPERS"),E1:E9999).
Week two of the month sees the last bit change from E1:E9999 to F1:F9999, week three to G1:G9999 and so on.
What this means is each month I am going to have to hack the formula about to reflect where the current data for each week lives; each new week sees a new column of data.
Is there a way to force Excel to automatically look at the correct column depending on which week/month it is? My limited knowledge tells me INDEX or MATCH will come into it at some point but I haven't got a clue how to code a formula that works.
All advice appreciated.