In Google Sheets add all the rows above the current one

Question

I want to add all the numbers in a given column of all the rows before the total row. Let's assume my column H contains number. If I have 15 rows, I'd do sum(H1:H15) in my sixteenth (total) row.

But when I add rows in between, my formula doesn't get updated, so I have to think of updating it to include all the rows.

I thought of using indirect to determine the index of the total row and doing the sum up to that row minus 1. That gave me:

=round(sum(indirect(concatenate("H2:H",row()-1))))

• We can ignore round() here - it's just to given the nearest integer for my sum
• sum adds all the rows
• indirect lets me convert a string into an address so it can be used in my sum function
• concatenate adds all the strings together
• "H2:H" is the beginning part of my range
• row() returns my current row and so row()-1 represents the last row I want to sum

Is there a more elegant way?

Thanks

Answer 1

You can use OFFSET for that like this:

=SUM(OFFSET(B6, 0, 0, ROW() - ROW(B6), 1))