Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Sum Same Cell Across Multiple Worksheets

1. Senior Member
Join Date
18th February 2004
Posts
252

## Sum Same Cell Across Multiple Worksheets

Hello all,

I have about 100 worksheets in my excel workbook and each sheet is identical. I would like to create a control sheet that would add the cells of the different worksheets together and place it in the control. I tried doing it manually but found out quickly that it would take forever since I have a about 10 cells that I need to put on the control sheet.

For example on the control sheet in J11 I want to put in: ='1'!J11+'2'!J11......'100'!J11

Is there a faster way to do this? Thanks for your help!

Excel Video Tutorials / Excel Dashboards Reports

2. Established Member
Join Date
14th July 2007
Posts
759

## Re: Add Same Cell From Different Worksheets

I'm not 100% sure what you're asking, but I'll give it a shot. If your sheets are named 1-100, then I think =SUM('1:100'!J11) is what you need. Let me know if I misunderstood the question.

Excel Video Tutorials / Excel Dashboards Reports

3. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Add Same Cell From Different Worksheets

Try:

=SUM('1:100'!J11)

4. Senior Member
Join Date
18th February 2004
Posts
252

## Re: Add Same Cell From Different Worksheets

Thanks much guys! that's exactly what I was looking for...thank you.

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
10th October 2011
Posts
11

## Re: Add Same Cell From Different Worksheets

Originally Posted by ByTheCringe2
Try:

=SUM('1:100'!J11)
Hi guys,

Sorry to bring an old topic back from the dead, but today I was looking for the same answer, just one step more complicated:

Does this also work for sheets that have actual (text) names?

thanks a lot!!

Excel Video Tutorials / Excel Dashboards Reports

6. I agreed to these rules
Join Date
10th October 2011
Posts
11

## Re: Sum Same Cell Across Multiple Worksheets

Sorry there, found the solution. So if anybody's looking to do the same:
Instead of sum('1:100'!A1), you take the name of the first sheet you like to sum and the name of the last one, like this:

Sum(Sheet1:Sheet3!A1)

Excel Video Tutorials / Excel Dashboards Reports

7. I agreed to these rules
Join Date
13th August 2012
Posts
1

## Re: Sum Same Cell Across Multiple Worksheets

Try the below one....

=sum('*'!J11)

Excel Video Tutorials / Excel Dashboards Reports

8. I agreed to these rules
Join Date
29th July 2015
Posts
1

## Re: Sum Same Cell Across Multiple Worksheets

I have tried all of the recommendations and they all work - except, when I try to copy the formula to another cell to get the value from the sum of those cells in the same collection of spreadsheets it doesn't work? When I view the formula it appears to have updated the cell reference, but the cell value is the same as the first cell I worked on. When I manually enter the formula (sum(sheet1:sheet3!a1) in cell a2 (Sum(Sheet1:sheet3!a2) it works? Why would it not update the value when I copy from a1 and paste in a2 but work when I manually enter the formula with the same syntax??

Thanks

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Sum Same Cell Across Multiple Worksheets

Hi kenm325
welcome to the forum
Please ask your question in the Excel forum, add a link to this post if you think it will help.

10. I agreed to these rules
Join Date
22nd August 2012
Posts
1

## Re: Sum Same Cell Across Multiple Worksheets

Hi All, Great info and solution to the original question. Instead of defining the tab range with '*' or Sheet1:Sheet3 how could I use a list of tab names? Therefore, if I create a list of tab names the Sum function would know to add the cell reference from each of the tabs listed.

Any thoughts are greatly appreciated.

Thank you

Excel Video Tutorials / Excel Dashboards Reports

#### Thread Information

##### Users Browsing this Thread

There are currently 5 users browsing this thread. (0 members and 5 guests)

#### Posting Permissions

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