# Summing a single cell Across Several Worksheets

I can easily sum a given cell, say B9, across multiple worksheets with something like:

``````=SUM(Sheet7:Sheet11!B9)
``````

This is the equivalent to :

``````=Sheet7!B9+Sheet8!B9+Sheet9!B9+Sheet10!B9+Sheet11!B9
``````

Which is the sum of cell B9 in all the worksheets from Sheet7 through Sheet11.

I need to create something like the first formula, but with the sheet names in cells. So if A1 contains:

Sheet7

and cell A2 contains:

Sheet11 Then the formula in A3 would use these cells. What I have tried so far is:

``````=SUM(A1 & ":" & A2 & "!" & B9)
``````

and

``````=SUM(INDIRECT(A1 & ":" & A2 & "!" & B9))
``````

Neither of these work.

I can easily create a VBA function to do this, but the workbook must be able to operate in a macro-free environment.

• try this `=SUM(INDIRECT(A1 & ":" & A2 & "!" & "B9"))` May 7 '15 at 20:55
• I don't think INDIRECT works well with multiple sheets. I can get all kinds of ranges to work with INDIRECT on a single sheet. May 8 '15 at 1:23

Based on @Dave’s answer and your comment `But I don't know in advance what the contents of cells A1 and A2 will be.......so I don't know how many terms to include.` I would suggest to add a column to include the total of each sheet and sum that column. Say you enter the sheet names in column `A`, then enter in column `B`:

``````=IF(ISNUMBER(INDIRECT(A2&"!B9")),INDIRECT(A2&"!B9"),0)
``````

Than you can do `=Sum(B:B)` to get the total.

Note that I added the `IF` and `ISNUMBER` function to avoid the `#REF!` when column A is missing a sheet name. You may want to use `ISBLANK` instead, as

``````=IF(ISBLANK(A2),0,INDIRECT(A2&"!B9"))
``````

If you do want to get an error message when column A has a wrong sheet name.

Another tip: If you name your sheets as Sheet1, Sheet2, you can easily drag to fill your sheets in column A

This is a manual approach which may not be ideal

``````=INDIRECT(A1&"!A1")+INDIRECT(A2&"!A1")
``````

or

``````=SUM(INDIRECT(A1&"!A1"),INDIRECT(A2&"!A1"))
``````

My worksheet looked like

``````     A         B         C
1    sheet2
2    sheet3
3
``````

And in Sheet2 A1 had a value as does Sheet3 A1

In your case, you would need

``````   =SUM(INDIRECT(A1&"!A1"),INDIRECT(A2&"!A1"),INDIRECT(A3&"!A1"),INDIRECT(A4&"!A1")...etc)
``````
• Thank you for this response. I tried the second formula and it correctly summed the values for Sheet7 and Sheet11, but none of the sheets in between. May 7 '15 at 14:11
• no sadly, you will need to enter each sheet as a new cell value in your worksheet1...
– Dave
May 7 '15 at 14:29
• But I don't know in advance what the contents of cells A1 and A2 will be.......so I don't know how many terms to include. May 7 '15 at 14:31
• You may want to update your question with that :(
– Dave
May 7 '15 at 14:41
• I have added the sentence which, hopefully clarifies my request......... ** once again thank you for your help. ** May 7 '15 at 14:46