Putting totals at top of column - inconsistent formulas

Ken1122 

Personally, I would introduce a named range 'Values' which refers to 

= E9:E1000

[ I have a somewhat harsh rule that applies to direct references:

1. If you actually know what it is then have the courtesy to name it, so others (or you, at a later date) know what the data represents

2. If you don't know what it is, delete it from the sheet so that it is not used in error.]

The formula 

= SUM(Values)

will cope with inserting further rows between row 9 and row 10 (as do the solutions already provided by Riny_van_Eekelen).

An alternative is to place headings on row 9 and turn rows 9-1000 into an Excel table.  If you do not like the repeated header row then row 9 can be hidden in its entirety.  The formula for the sum is then

=SUM(Table1[Value])

and will update automatically if you provide more meaningful names for the table and field to be summed.

The Table will allow you to insert data at row10 or append it at row1001 as you prefer.

Note: The italicized text is intended to be controversial but not a personal attack.