Automatic Totals

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

 

[Gordon]

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.

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

 

[Roger Govier]

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.

 

[Zygy]

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.

 

[Gordon]

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.

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.

 

[Sandy Mann]

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

 

[Gord Dibben]

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

 

[Zygy]

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?

 

[Gord Dibben]

Yes, the formula would be entered into C80


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


Gord

 

[Zygy]

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