Question posted 2012 · +18 upvotes
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.
Thanks!!
Accepted answer +39 upvotes
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.
2 code variants in this answer
- Variant 1 — 1 lines, starts with
=SUM(A2:A) - Variant 2 — 1 lines, starts with
=SUM(A2:A1048576)
Top excel Q&A (6)
- Shortcut to Apply a Formula to an Entire Column in Excel +335 (2011)
- How should I escape commas and speech marks in CSV files so they work in Excel? +136 (2012)
- Convert xlsx to csv in linux command line +96 (2012)
- How to create a link inside a cell using EPPlus +50 (2011)
- IF statement: how to leave cell blank if condition is false ("" does not work) +44 (2013)
- T-SQL: Export to new Excel file +44 (2012)
excel solutions on this site
— top 6%.