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!

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

  • Re: Add Same Cell From Different Worksheets

    Quote from ByTheCringe2;378189



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

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


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


  • 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

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


    S O

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!