I have this, in D1:


I want D1 to always be the sum of the rest of column D (D2 is a header)

I sometimes move a row, from eg 8 to 3, and that causes D1 to change to sum(d4:d).

I want D1 to stay as my sum of the column.

4 Answers 4


Short answer



Google Sheets will update A1 notation references when rows or columns are inserted, so if you need that a reference never changes, instead you have to use a string and the INDIRECT() function to convert it to a reference.


  • so, it's kinda like eval() ? (php) Commented Jan 21, 2016 at 20:55
  • @DougCassidy: I don't think so. INDIRECT only returns a cell/range reference, it don't execute code. Commented Jan 21, 2016 at 21:05

With INDIRECT, I had trouble when I inserted a new row or column and everything shifted and that's where OFFSET comes in.

Use =sum(OFFSET(D2:D,1,0))

This will make sure your formula is correctly updated when you insert cells and when you copy paste them.


Easier way: =sum(D$3:D). the $ locks the starting row.

  • 4
    No, this does not do what the OP wants. If you insert a row on top, the formula will be changed to =sum(D$4:D). He wants it to start from the 3rd row, even if new rows are added/deleted/moved.
    – user79865
    Commented Jan 23, 2016 at 6:26
  • You are right. It looks $ only locks the value when copying formulas
    – daniel
    Commented Jan 23, 2016 at 17:42
  • @user79865 is there a way to apply the formula for the inserted above row? Commented Mar 3, 2021 at 23:22

I'm becoming acutely aware that certain functions that interpret contents are very time consuming. Indirect is one of the ones I avoid.

Another approach is to put a buffer row at the start and end of the row range. You can make the height small, and fill with a colour or hide the row.

So Row 1 is headers Row 2 is whatever you currently have as row 2. Row 3 is your buffer row. D1 = sum(D$3:D)

Now you can rearrange as you want.

If you include Row 3 in your frozen header section, you can even sort your spreadsheet. It won't affect the frozen rows.

Your Answer

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

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