Forum Discussion
Putting totals at top of column - inconsistent formulas
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 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.
Ken1122 How does your SUM formula look like?
It should be like =SUM(A9:A100). Inserting cells A10 and A11, for instance, would change it to =SUM(A9:A102)
If not, please upload your file so that I can check it.
Riny_van_Eekelen It is not working the way you described. I'd rather not post the file as it contains personal data. Thanks.
Ken1122 You can't sum the entire column and have the SUM in it, as it will result in a circular reference. But you can try an indexed sum range like in the attached example. Not very straight-forward but I believe it works how you would need it to.
