# 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

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

• Re: Add Same Cell From Different Worksheets

Quote from ByTheCringe2;378189

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

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

• Re: Sum Same Cell Across Multiple Worksheets

Try the below one....

=sum('*'!J11)

• 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

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

Regards,

S O

## Participate now!

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