7

I have this, in D1:

=sum(D3:D)

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.

17

Short answer

Use =SUM(INDIRECT("D2:D"))

Explanation

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.

References

  • so, it's kinda like eval() ? (php) – Doug Cassidy Jan 21 '16 at 20:55
  • @DougCassidy: I don't think so. INDIRECT only returns a cell/range reference, it don't execute code. – Rubén Jan 21 '16 at 21:05
2

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.

1

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

  • 2
    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 Jan 23 '16 at 6:26
  • You are right. It looks $ only locks the value when copying formulas – daniel Jan 23 '16 at 17:42
0

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, privacy policy and cookie policy

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