3

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

enter image description here

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.

3
  • try this =SUM(INDIRECT(A1 & ":" & A2 & "!" & "B9")) May 7, 2015 at 20:55
  • @KingOfAllTrades Sadly, I get #REF! May 7, 2015 at 22:00
  • 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, 2015 at 1:23

2 Answers 2

1

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

0
1

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)
5
  • 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, 2015 at 14:11
  • no sadly, you will need to enter each sheet as a new cell value in your worksheet1...
    – Dave
    May 7, 2015 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, 2015 at 14:31
  • You may want to update your question with that :(
    – Dave
    May 7, 2015 at 14:41
  • I have added the sentence which, hopefully clarifies my request......... ** once again thank you for your help. ** May 7, 2015 at 14:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .