4

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?

Improve this question
1
  • 2
    What's in D3? Could you just use =SUM(D3:D102)? Inserting a row would expand your range, because the row is inserted "within" that range and not "above" it. After an insert, it would become =SUM(D3:D102). If There is a value in D3 you don't want to add, you can use =SUM(D3:D102)-D3. If there is a label in D3, Google won't bother and just use the value 0 (zero) for that label.
    – agtoever
    Mar 19, 2015 at 12:47

3 Answers 3

Reset to default
2

agtover's comment to the question works fine: Extend the function range to D3 (where the 'Amount' headline is), and then whenever a new row is added, only the end of the function range is updated.

Improve this 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.

Improve this answer
0

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.

Improve this answer
3
  • I was also thinking this could do the trick, but unfortunately it doesn't. If I add a new row above row 4 (i.e., add a new expense), the formula changes to =SUM(D$5:D102).
    – John Manak
    Mar 19, 2015 at 12:43
  • You didn't mention this in your question, so I answered accordingly. In the case you now described, you have to adjust manually. The sheet can only do what you tell it. It has no idea where you will add the next row.
    – CharlieRB
    Mar 19, 2015 at 12:47
  • I did describe it. The dollar signs locks the cell in case you move the function from one cell to another; it however doesn't lock it in case you add a new line. agtover's comment to the question gives a hacky solution to the problem.
    – John Manak
    Mar 19, 2015 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.