Google Spreadsheet sum which always ends on the cell above

Question

How to create a Google Spreadsheet sum() which always ends on the cell above, even when new cells are added? I have several such calculations to make on each single column so solutions like this won't help.

Example:

On column B, I have several dynamic ranges which has to be summed. B1..B9 should be summed on B10, and B11..B19 should be summed on B20. I have tens such calculations to make. Every now and then, I add rows below the last summed row , and I want them to be added to the sum. I add a new row (call it 9.1) before row 10, and a new raw (let's call it 19.1) before row 20. I want B10 to contain the sum of B1 through B9.1 and B20 to contain the sum of B11:B19.1.

On excel, I have the offset function which does it like charm. But how to do it with google spreadsheet? I tried to use formulas like this:

=SUM(B1:INDIRECT(address(row()-1,column(),false)))   # Formula on B10
=SUM(B11:INDIRECT(address(row()-1,column(),false)))  # Formula on B20

But on Google Spreadsheet, all it gives is a #name error.

I wasted hours trying to find a solution, maybe someone can calp? Please advise

Amnon

Answer 1

You are probably looking for formula like:

=SUM(INDIRECT("B1:"&ADDRESS(ROW()-1,COLUMN(),4)))

Google Spreadsheet INDIRECT returns reference to a cell or area, while - from what I recall - Excel INDIRECT returns always reference to a cell. Given Google's INDIRECT indeed has some hard time when you try to use it inside SUM as cell reference, what you want is to feed SUM with whole range to be summed up in e.g. a1 notation: "B1:BX".

You get the address you want in the same way as in EXCEL (note "4" here for row/column relative, by default Google INDIRECT returns absolute):

ADDRESS(ROW()-1,COLUMN(),4)

and than use it to prepare range string for SUM function by concatenating with starting cell.

"B1:"&

and wrap it up with INDIRECT, which will return area to be sum up.

REFERRING TO BELOW ANSWER from Druvision (I cant comment yet, I didn't want to multiply answers)

Instead of time consuming formulas corrections each time row is inserted/deleted to make all look like:

=SUM(INDIRECT(ADDRESS(ROW()-9,COLUMN(),4)&":"&ADDRESS(ROW()-1,COLUMN(),4)))

You can spare one column in separate sheet for holding variables (let's name it "def"), let's say Z, to define starting points e.g. in Z1 write "B1" in Z2 write "B11" etc. and than use it as variable in your sum by using INDEX:

SUM(INDIRECT(INDEX(def!Z:Z,1,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - sums from B1 to calculated row, since in Z1 we have "B1" ( the 1,1 in INDEX(...,1,1) )

SUM(INDIRECT(INDEX(def!Z:Z,2,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - sums from B11 to calculated row, since in Z2 we have "B11" ( the 2,1 in INDEX(...,2,1) )

please note:

1. Separate sheet named 'def' - you don't want row insert/delete influence that data, thus keep it on side. Useful for adding some validation lists, other stuff you need in your formulas.

2. "Z:Z" notation - whole column. You said you had a lot of such formulas ;)

Thus you preserve flexibility of defining starting cell for each of your formulas, which is not influenced by calculation sheet changes.

By the way, wouldn't it be easier to write custom function/script summing up all rows above cell? If you feel like javascripting, from what I recall, google spreadsheet has now nice script editor. You can make a function called e.g. sumRowsAboveMe() and than just use it in your sheet like =sumRowsAboveMe() in sheet cell.

Note: you might have to replace commas by semicolons