3

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?

migrated from superuser.com Mar 23 '15 at 19:50

This question came from our site for computer enthusiasts and power users.

  • 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 '15 at 12:47
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.

1

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.

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.

  • 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 '15 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 '15 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 '15 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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