# 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.)

- Posted In:
- Uncategorized

###### craig

All stories by: craigThis site uses Akismet to reduce spam. Learn how your comment data is processed.

## Leave a Reply

You must belogged in to post a comment.