Update formula ranges when adding new rows to a spreadsheet

Question

I have a Google Spreadsheets in which I keep a list of expenses. I add a new expense by inserting a new row above the last expense. On the first row of the spreadsheet, I keep a sum of all expenses: =SUM(D4:D101).

However, when I add a new row, the function range shifts down by one row to =SUM(D5:D102), i.e. it is still covering the previous range of expenses, but I would like it to include the most recent one on line 4 as well: =SUM(D4:D102).

As I add new rows quite often, I would like the spreadsheet to do this automatically.

Do you know how to achieve this?

Answer 1

Credit to agtover who provided the solution in a comment

I extended the function range to include D3 which contains the column heading 'Amount' which the SUM function ignores.

Using =SUM(D3:D101), when a new row is inserted above D4, the range in the formula expands down and includes it: =SUM(D3:D102).

Answer 2

Use the indirect function.

=sum(indirect("D4:D102"))

Indirect allows you to specify a cell range with a string, and this string will not be updated by Google when you add and shift columns. It will always refer to the same range.

Answer 3

You are using relative reference, which allows the sheet to assume you want to keep the range size the same, just move it. To keep it anchored, you need to use absolute referencing in your formula. This keeps it from moving.

=SUM(D$4:D101)

Using the $ before the 4 tells it to lock the top of the range to the 4th cell.