Announcement

Collapse
No announcement yet.

Sum Same Cell Across Multiple Worksheets

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    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.

    Comment


    • #3
      Re: Add Same Cell From Different Worksheets

      Try:

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

      Comment


      • #4
        Re: Add Same Cell From Different Worksheets

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

        Comment


        • #5
          Re: Add Same Cell From Different Worksheets

          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!!

          Comment


          • #6
            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)

            Comment


            • #7
              Re: Sum Same Cell Across Multiple Worksheets

              Try the below one....

              =sum('*'!J11)

              Comment


              • #8
                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

                Comment


                • #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.
                  If the solution helped please donate to RSPCA

                  Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

                  Comment


                  • #10
                    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

                    Comment


                    • #11


                      Re: Sum Same Cell Across Multiple Worksheets

                      Hi pecochran1, welcome to OzGrid.

                      Please see above where previous users have been asked to post their question in a new thread. Asking your own question in another persons thread is not allowed so please start a new one and provide a link back to this one if needed.

                      Regards,

                      S O

                      Comment

                      Working...
                      X