Variable column sum

[Cynthia Moore]

I frequently need to sum a column of numbers where I will also need to add rows. This works fine unless I add a row at the top or bottom of the range. Then the sum range gets messed up.

B
3 Cost
4 $12.95
5 $4.99
6 $23.75
7 $41.69

The formula in B7 is =SUM(B4:B6).

If I insert a row between 3 & 4 or between 6 & 7, the sum range is wrong.

My solution has been to use the Offset function inside the Sum function.

Please Login or Register  to view this content.

Is there a simpler way?

[mehmetcik]

Half your problem will disappear if you use:=

Please Login or Register  to view this content.

if inserting at the last row
then copy, insert paste the last row
then over type

ie select row by clicking on the row number
right click and copy
right click insert copied cells

[Blake 7]

You could name your ranges - ie name cell B4, B5 and B6 and then sum the names and then sum =Sum(name1,name2,name3)

[newdoverman]

If the column of values in column B has a title, you could turn that range of cells into a Table by selecting the cells including the title and Insert Table. On the design tab select Total Row, Header Row and uncheck Banded Rows if you don't want the usual bands present in a table. After doing this, you can insert rows under the header and above the total as much as you like and the total will automatically update.