sum multiple sheets based on condition

**Mile029** · 05-10-2011, 06:29 PM

I need help, I have 12 months of data, each in a separate tab. I have a summary tab summing all the months. for example in cell C7 of the summary tab it sums all the months Jan-Dec C7's. But I want to condition this information. I want to enter "from" and "to" months on my summary tab and have it sum just those months.

so for example if on the summary tab
"From" cell A1 = Jan and
"To" cell A2 = Feb
in the summary tab's cell C7 it sums Jan C7 + Feb C7.

But if
A1 = Jan and
A2 = Mar
in the summary tabs cell C7 it sums Jan C7 + Feb C7 + Mar C7

is this possible? please help. Thanks.

**Richard Buttrey** · 05-10-2011, 08:03 PM

Hi,

The first choice would have been an INDIRECT() function but Excel has trouble when using the INDIRECT function with 3D ranges as you are doing here. The only way I know is as follows.

Create a list of your month tab names, presumably Jan,Feb,Mar etc.. in say A5:A16.
Now create a dynamic range name called 'SheetList' and define the name as

=OFFSET(Summary!$A$5,Summary!$B$1-1,0,Summary!$B$2,1)

In B1 enter

=MATCH(A1,$A$5:$A$16,FALSE)

and copy this to B2. These will return the month number.

Finally in C7 enter the following array formula, i.e. with Ctrl-Shift-Enter

=SUM(N(INDIRECT(SheetList&"!"&"C7")))

See the attached as an example workbook

**Mile029** · 05-25-2011, 04:59 PM

yes, that worked perfectly, thanks Richard!

Only issue is you can't carry over the formula to the next cell and it adjusts to the next cell, you have to manually type the next cell. "C7" remains C7 when copied into C8. Did it manually, but would like it to change based on inseerting and deleting lines. Thanks for your help.