MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summation with multiple sheets in a workbook

Posted by Enrico on August 25, 2001 4:02 PM

I have a workbook that contains many sheets. Each sheet has a cell with a number. I want to have another cell on one of the sheets to act as a total to all of the balance columns. This means I want to take the cell on each sheet that has a number, add them together, and place the result in a cell that calculates the total. Can anyone help me out? If you need more info please feel free to email me.



Posted by Robb on August 25, 2001 5:26 PM


Select the cell in which you want the total, then:

- Type =SUM(
-If you look in the formula bar, you will see =SUM(
-select the first cell containing a number you wish to add
-Its address will be added to the formula
-Formula bar will now show something like =SUM(Sheet2!B8
-Type a comma
-Formula bar will now be =SUM(Sheets2!B8,
-Select the next cell and type a comma
-Formula bar reflects this =SUM(Sheets2!B8,Sheets3!D4,
-Continue until you have done this for all the cells you wish to include
-After the last cell DO NOT TYPE A COMMA
-After the last cell address type )
-Formula bar now =SUM(Sheets2!B8,Sheets3!D4, etc etc ,Sheets15!F34)
-Hit enter

You will go back to the original cell and the total should be showing.

Any help?

Posted by Trevor on August 25, 2001 7:07 PM

Or, if .....

Or if the cell on each sheet has the same address :-


Or :-



Posted by Robb on August 25, 2001 9:55 PM

Re: Or, if .....

True Trevor. For some reason, I assumed they were all over the place.


Or if the cell on each sheet has the same address :- =SUM(Sheet1:Sheet5!A1) Or :- =Sum(Sheet1:Sheet5!A1,Sheet8:Sheet9!B1) etc.