Excel 2007 : sum of same cell on multiple worksheets

**mrsfrogge** · 12-31-2009, 03:38 PM

am trying to figure out formula to calculate the total sum of the same cell on 20 different worksheets.

is there a easier way to do this without clicking or typing each sheet? is there a formula that will take from a range of sheets?

**NBVC** · 12-31-2009, 03:49 PM

Something like:

=Sum('Sheet1:Sheet20'!A1)

Will sum A1 from all sheets between Sheet1 and Sheet20.

**scaffdog845** · 12-31-2009, 04:00 PM

NBVC's solution will give you the desired results. If you believe the workbook may grow in the number of sheets, you can use a common trick of creating a new blank sheet called START and make sure it is the very first sheet in the workbook then create a blank sheet called END and make sure it is the last sheet in the workbook. Use the formula

Please Login or Register  to view this content.

As long as you add new sheets between START and END the formula will maintain them in the total without the need to edit the formula after each new sheet is added.

**gibson2503** · 04-12-2013, 09:18 AM

I have about 110 worksheets and I am trying to do the same thing. However, I keep getting 0.00 for the result when some of the sheets show 0.00 and some of them show something higher or lower than 0.00. My first sheet is called Totals. The next is called START. Then I have Sheet 2, Sheet 3, etc, all the way to Sheet 110. Then, my last sheet is called END. I need the sum of cell J6 on Sheet 2 through Sheet 110. I want that total to appear in cell B2 of the Totals sheet. I typed =SUM(START:END!J6) and all I get is 0.00 .

**Siops** · 09-19-2013, 02:12 PM

Instead of using START:END, FIRST:LAST, is there a way if my worksheet name is like this "May 1", "May 2", "May 3", and so on? Thank you!

**Debraj Roy** · 09-19-2013, 02:15 PM

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**Siops** · 09-19-2013, 02:18 PM

Sorry Sir. I just thought it's related with the same topic. Thank you for the warning anyway.

**GHONDA** · 09-18-2014, 04:31 PM

I have the same issue as you, did you end up finding help on this?

**scottiex** · 09-18-2014, 06:03 PM

Try this for summing later sheets
Sumlatersheets(range)

and the below

Please Login or Register  to view this content.

**protonLeah** · 09-18-2014, 10:22 PM

GHONDA,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.