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.

Answer 3

One way you can do this in both Excel/Google Spreadsheets is to add a dummy column with a single value at the top (and all blanks for the remainder of the column).

You can then do a SUMIF to summarize all values except the one which is not "" - which should just be the cell A1 - put the following in B1, with whatever numbers below B1 you want and anything in A1.

=SUMIF(A:A,"",B:B)

Answer 4

=(B3+C3+D3+E3+F3+G3+H3+I3+J3+K3+L3+M3)

This worked for me. 3 being the row number When you Drag the box downward the formula transfers down and changes the row number automatically for each row.

After I put another heading several rows down adding the word SUM into the formula worked.

=SUM(B19+C19+D19+E19+F19+G19+H19+I19+J19+K19+L19+M19)

Not sure if it was necessary. But just copy pasting the first formula I used into the lower segment made an error message.