Discussion Locked

Excel subtotaling

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Is there a way that i can make excel 97 or 2000 give me a subtotal for each page columns as a footer and then give me a final grand total at the end of the last page. I know this is possible with VBA but iam not good at that. any help out there will be appreciated

3 replies

by boschke

On each page (or sheet), insert the formule for making the column total. You should make sure all the subtotals are in the same cell
on each sheet (for example E50 can be =SUM(E1:E49) ).
So far so good for the subtotals.

Excel allows you to define ranges for summations, both over different sheets/pages as cells. This means you can sum the subtotals over the different sheets (using =SUM(Sheet1:Sheet9!E50) ) or you can just make the summations all over again. ( =SUM(Sheet1:Sheet9!E1:E49) will sum cells on sheets 1 through 9, from cells E1 through E49) The exclamation mark works as a divider to mark the worksheet the data is on, and the colon is the way to define the range, as you must already know.

I am using a dutch language version of excel 2000, so I hope the function names are correct.

by mzee

i do not want do define a specific cell for subtotals, cause sometimes i will be deleting cells in worksheets and that will throw off my subtotals, what i want is that no matter how any deletions on the sheet the totals should come on the footer

by mackem

This is similar to the last answer, but should take care of deletions. Follow the same steps for the sub totals and define a cell for the sub total.
When it comes to the grand total use =SUM and then click on each sub total on each page. Excel remembers the cell and if any rows added or deleted will alter the calculation for you.

This discussion is closed to new replies.