# SUMPRODUCT and INDEX/MATCH?

#### chuckles1066

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

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Dave Patton

##### Well-known Member
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

##### Banned
Once again this board comes up trumps with the answer

Thanks ever so much.

Replies
3
Views
283
Replies
11
Views
232
Replies
3
Views
932
Replies
1
Views
111
Replies
0
Views
151

1,191,683
Messages
5,987,990
Members
440,124
Latest member
dippy_egg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back