Sum Across Variable Range of Sheets

**QuestaTH** · 10-14-2010, 02:28 PM

I’m trying to write a formula that sums the same cell in a variable range of sheets, where I can enter the names of beginning sheet and ending sheet of the range in cells to be used in the formula (simple example attached), i.e., something with the same result as:

=Sum(Sheet1:Sheet3!A1)

But where I can change Sheet1 and Sheet3 to the values in cells C1 and C2 (e.g., June 2010 and September 2010)

It seems like something like this should work:
=SUM(INDIRECT("'"&C1&"'!A1"):INDIRECT("'"&C2&"'!A1"))

The closest I found from another forum is:

=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C4&"'!A1"),"<>=0"))

…but it only sums the first and last sheets, not the sheets in between. Does this require a macro?

Thanks in advance for any advice….

**JBeaucaire** · 10-14-2010, 02:47 PM

The only way I've been able to resolve this kind of INDIRECT() formula is to list all the sheets in a range somewhere, name that range of cells then use that in an array formula. See attached.

**QuestaTH** · 10-14-2010, 05:11 PM

Thanks, Jerry. My boss is looking for something to avoid having to list all the sheets, since he will be adding them on an ongoing basis and wants to be able to sum different sheet ranges at different times. I'll run this by him, though. In the meantime, I'll leave the thread "unsolved" in case there are other suggestions....
Thanks again!