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, 503 etc.

Ramrod

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.

Balaklava

However, if you then add a row just below the total row, the SUM is changed to start from A3, when you want it to stay at A2. How to stop it moving the start of the range? – Ungrateful

@MaxWaterman you can "set the range in stone" using INDIRECT: =SUM(INDIRECT("A2:A")). – Balaklava

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.

Prang

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)

Moonshiner

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.

Crural

Answer 5

=SUM(A1:A500) simply by using this whenever the rows grows up in the spreadsheet. Just use drag mode by tap on the entire row and click till you want.

Haifa