Google Sheets: Sum All Rows Above “Me”
Tracking my aviation journey via a handwritten logbook and a spreadsheet, I got annoyed by having to update the Totals row, every time I inserted another 20 rows above it, so each column’s formula would include those 20 new rows in the =SUM(…) formula
I did some online searching and learned about the very helpful =INDIRECT function, as well as simple string concatenation, including a formula that was almost copy-and-paste, except that it hard-coded the Column designator (e.g. A, B, etc), though it did handle specifying rows from 2 (just below my header row) through the row just above the Total cell in question.
Not wishing to spend 45 seconds editing those column designators for the entire spreadsheet, I instead spent probably about 20 minutes (yes, I’m a programmer, haha) coming up with this more-flexible formula that derives the current column as well, in the hopes that I’d be able to use this in other situations:
=sum(indirect(regexreplace(address(row()-1,column(),3),”[$]([A-Z]*)([0-9]*)”,”$12:$1$2″)))
In short, what this formula does is
- compute the previous row’s number: row()-1
- compute the current column number (NOT name!): column()
- uses address(r, c, 3) to turn that into a column-letter-plus-row-number string, such as $H231
- uses regexpreplace() to extract that alphabetic component (into group $1), which is the column letter, and the row number (into group $2)…
- …then has that function return a replacement string (e.g. H2:H231) suitable for use in =SUM()
- wraps that replacement string in an =INDIRECT(), which means it’s an evaluated (not constant/static) expression
- passes that to =SUM()
The above expression can this be pasted into any cell and will compute the sum of all the cells above it (except the first one, the header row, aka Row 1).
Hope this helps someone out! (I tried adding it as an answer to a circa-2019 question on a Google discussion forum, but that question was closed to comments.)