# Thread: Sum a single cell across 31 worksheets

1. ## Sum a single cell across 31 worksheets

I have inherited a set of workbooks along with the task of summarizing their results, twelve workbooks with up to 31 worksheets in each. I'm looking to consolidate the total of columns C through G in row 11. On the 31st sheet a SUM exists in column AB already but you can imagine what it looks like (=AA11+'1-30'!AA11+'1-29'!AA11+'1-28'!AA11+'1-27'!AA11+ . . . . and so on).

There are 24 columns (for Hours of Day). If I add a new sheet, click on C11, hit the plus key, select all sheets, then click on C11 in the first sheet, I get a formula that looks like this: =+'1-1:1-31'!C11, but it returns #REF.

Is there a shortcut you can suggest to help me speed this process? I may be asked to do prior years and so far they're all looking the same.

2. A 3D formula will work for contiguous sheets
=SUM('1-31:1-01'!AA11)

Steve

3. I pasted your formula as it appears here and changed AA to C to refer to the first column of data. Excel changes the formula to =SUM('1-31:[1-01]1-01'!C11) and results in #REF.

I tried the Consolidate function just now stepping through all 31 sheets and the error popped up Cannot Create something or other.

Could the problem be that the worksheet labelled 1-1 appears as Sheet1 in the Consolidate function All references box? All other sheets appear to be labelled '1-4 or '1-5 etc.

4. Steve, Please ignore the Consolidate function error. That was my fault. I got a reulting value but want to check it out to see if its really working the way I'm expecting.

But that was very labor intensive and I have many more files to go. I'd really like to get your suggestion to work to save time. Any hints?

5. Arcturus,

Should work exactly as Steve said, it does for me at least in this limited example.

FYI: Cell A1 contains the value of 5 on each of the dated sheets.

6. Thanks Steve. I think I got it now. =SUM('1-1:1-31'!AA11)

The 3D formula you showed me is correct and I think it works just fine after one simple modification. The original spreadsheet was created with non-standard date formats. You naturally (and rightly) assumed that the first sheet for the first day would start with a leading zero. Once I pulled that out of the formula the correct value (manually verified) popped up.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•