SUMPRODUCT and INDEX/MATCH?

[chuckles1066]

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.

 

[Dave Patton]

Row 1 criteria and C1 relevant column to sum such as 5
Data area is named rTbl

=SUMPRODUCT(--(INDEX(rTbl,,1)="Walmart"),--(INDEX(rTbl,,2)="Newspapers"),(INDEX(rTbl,,5))) or
=SUMPRODUCT(--(INDEX(rTbl,,1)=A1),--(INDEX(rTbl,,2)=B1),(INDEX(rTbl,,C1)))

 

[chuckles1066]

Once again this board comes up trumps with the answer

Thanks ever so much.