# combining a sumproduct formula with an indirect function

#### smerrick

##### Active Member
I have been using the sumproduct formula recently and I must say that this has been very impressive for all sorts of calculations and analysis.
I was wondering if this could be combined with an indirect formula as I have a spreadsheet that currently has over 100 rows (and is still growing). I am currently using excel '03.
The scenario I have is this: the numerous tabs all contain the same format of data i.e. column A = Customer, column B = Product, column C = Net Sales, column D = Cost Value, column E = Margin.

I also have another tab called Summary. I already have entered all of the tab names in column A e.g. 'Sector_Month[MMM]_Year[YY]. I have also added in a data validation box (3 altogether) which will let the user select customer, product and if they want either net sales, cost value of margin.
Now depending on the users selection, can a sumproduct on an indirect function be used so that it would automatically update the values next to column A (the typed in tab name)?
I could do this task by typing in the formula in each and every tab but this would obviously take a bit of time to do.
Any help would be appreciated.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Andrew Poulsom

##### MrExcel MVP
Take the formula:

=SUMPRODUCT(--(Sheet2!A1:A10="A"),Sheet2!B1:B10)

If A1 contains the text Sheet2, with INDIRECT it would become:

=SUMPRODUCT(--(INDIRECT("'"&A1&"'!A1:A10")="A"),INDIRECT("'"&A1&"'!B1:B10"))

#### smerrick

##### Active Member
Thank you again Andrew for your expertise - truely remarkable.

Replies
1
Views
83
Replies
11
Views
324
Replies
2
Views
324
Replies
6
Views
354
Replies
3
Views
76

1,191,690
Messages
5,988,037
Members
440,125
Latest member
vincentchu2369

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