Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Sum Same Cell Across Multiple Worksheets

  1. #1
    Join Date
    17th 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. #2
    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. #3
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Add Same Cell From Different Worksheets

    Try:

    =SUM('1:100'!J11)
    .

  4. #4
    Join Date
    17th 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. #5
    Join Date
    10th October 2011
    Posts
    11

    Re: Add Same Cell From Different Worksheets

    Quote Originally Posted by ByTheCringe2 View Post
    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. #6
    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. #7
    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. #8
    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. #9
    Join Date
    9th April 2007
    Location
    Alstonville, Australia
    Posts
    4,505

    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.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 5
    Last Post: December 26th, 2010, 06:17
  2. List Same Cell From Multiple Worksheets
    By whitegoldf150 in forum EXCEL HELP
    Replies: 11
    Last Post: November 22nd, 2010, 05:56
  3. Select Multiple Worksheets Based On Cell
    By patsguru06 in forum EXCEL HELP
    Replies: 7
    Last Post: June 24th, 2008, 09:57
  4. Sum Up Multiple Cell Ranges On Multiple Worksheets
    By alexander in forum EXCEL HELP
    Replies: 5
    Last Post: January 24th, 2008, 07:44
  5. Replies: 3
    Last Post: October 12th, 2007, 10:02

Bookmarks

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