Automatic Totals

  • Thread starter Thread starter Zygy
  • Start date Start date
Z

Zygy

How do I arrange for the sum totals in the last line on a worksheet with
several columns of figures will automatically adjust itself when I enter an
additional line or lines of figures, so that I do not have to use the
totalling tool every time I make entries on the worksheet.
 
Zygy said:
How do I arrange for the sum totals in the last line on a worksheet with
several columns of figures will automatically adjust itself when I enter
an additional line or lines of figures, so that I do not have to use the
totalling tool every time I make entries on the worksheet.
Click to expand...


Make sure that the range summed contains one or more extra rows - then just
insert your new rows before the end of the range....
 
Hi

If you have XL2003, you can use Data>List>Create list which will
automatically extend formulae as you add more rows to the list, with a
toggle to switch on or off the Totals at the bottom of the list.

For XL2007, an improved version exists with Insert>Table.
 
Hi,

I tried it and it worked only when I included the additional lines in the
Sum but when I added another line after that your suggestion did not work.
So, what did I do wrong?
Incidentally this is on Office 2000.
 
Zygy said:
Hi,

I tried it and it worked only when I included the additional lines in the
Sum but when I added another line after that your suggestion did not work.
So, what did I do wrong?
Incidentally this is on Office 2000.
Click to expand...

If you sum (say) A1:A35, but your actual data is A1:A25 and you insert a row
at A26, then the sum should still work.
 
With, as an example, data in B2:B14 and this formula in B15

=SUM(B2:OFFSET(B15,-1,0))

If you select Row 15, (the one with the formula), or any row above it and
insert a new row the formula will adjust to accommodate the new row.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Zygy

Assuming your current data is in A1:A10

Enter this in A11

=SUM(A1:INDEX(A:A,ROW()-1))

As you insert rows above A11 it will include the new rows.


Gord Dibben MS Excel MVP
 
Just to make it absolutely sure I have the formula right. My entries in
Col.C are up to line 79, so my formula to enter in Col.C line 80 should
read -

=SUM (C1:INDEX(C:C,ROW()-1)) Am I correct?
 
Yes, the formula would be entered into C80


BTW............get rid of the <space> after =SUM


Gord
 
Your formula works fine! Thank you and thank you also to all others who
contributed to my posting.
 
Back
Top