Sum of row n through last row

Question

I want to create a TOTAL row at the top of my spreadsheet. In this row, each cell should be the SUM of the values in the column below the TOTAL row.

So for example, if the total row is Row 1, cell A1 should be the SUM of A2 through the last row in column A. The number of rows in the spreadsheet will grow over time, so I can't just say SUM(A2:A500) because eventually there will be row 501, 502, etc.

Answer 1

If you want something that just works in Google Spreadsheets (as the title suggests), you can use open-ended ranges:

=SUM(A2:A) 

In Excel, you can specify the maximum rows for that version; for example, for 2007 and 2010:

=SUM(A2:A1048576) 

This will work in Google Spreadsheets as well, and is beyond the current theoretical row limit in GSheets (400000)*.

Edit: *The quoted limit of the new version of Sheets is actually now 2 million cells.

Answer 2

Try this

=SUM(OFFSET(A1,1,0,ROWS(A:A)-1,1)) 

Works in both Excel and Google Docs

One downside: by using OFFSET the formula becomes Volatile ie is calculated every time the sheet calculates irrespective of data change.