Forum Discussion
Putting totals at top of column - inconsistent formulas
My spreadsheet has about 8 columns. Each column is totaled on line 8 under the heading. I add new entries to individual columns by inserting new lines on line 10, which moves everything down and keeps the latest transactions at the top. On line 8 each column formula is, e.g., SUM(E10:E1000). The problem is that each time I insert new lines the formula is modified, e.g., after inserting a few lines in other columns
the formula becomes SUM(E15:1005). This causes inconsistent formula errors and having to redo the formula every time I make an entry. Is there a way to lock the formulas so they don't change when new lines are inserted? What is the solution?
Thanks, Ken
8 Replies
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.
Ken1122 Not sure I can visualise exactly what you are describing, but you could consider to include an empty row in the sum range, directly below the SUM formula. Inserting rows directly below the empty row will fix the start of the sum range to the header row and automatically expand the sum range at the bottom. See example attached.
Riny_van_Eekelen What I need to do is use a formula that will automatically sum the entire column rather than using a numerical formula designating the cells. Is that possible? Thanks
Riny_van_Eekelen Thanks for your response. I tried doing that but it doesn't help. The totals are on line 8, line 9 is blank, and the entries start on line 10. So every time I make a new entry I put the cursor on line 10, right click, click on Insert Line, add the new entry. This changes the formulas and creates the problem. I'm not an expert at Excel, am I missing something? Thanks, Ken
Hello Ken1122
if the formula sums cells A10:A1000 and you insert a row in row 10, then the sum range will adjust. That is just how Excel works.
If you don't want the formula to include new rows, then insert these rows outside of the range that the formula refers to, i.e. select a cell in row 9 and insert a row there.
To make this easier, you may want to use a helper row just for that purpose, i.e. inserting rows above the summed range that will not change the sum range.
