Update SUM when new row is inserted - can I copy this formula

[bchez]

I have a formula in cell I22 =SUM(INDIRECT("I2:I"&ROW()-1)) which will update the SUM if I insert new rows. However, when I copy this formula to another column, L for example, the formula still says Column I. Any way to make the formula copy and paste friendly? Thanks!

 

[Peter_SSs]

You could try this instead. The I14 in this formula is the address of the cell that the formula is actually in. When copied to other cells or new rows inserted it will adjust accordingly.
I assume that you are aware that this and your original suggestion are volatile functions?

=SUM(OFFSET(I14,-1,,1-ROW()))

 

[StephenCrump]

Another way ...

In cell A2, define a range name: CellAbove = !A1

Then you can use: =SUM(I$2:CellAbove)

 

[Peter_SSs]

Then you can use: =SUM(I$2:CellAbove)

Unless the inserted (or deleted) row(s) are at the top of the range?

 

[Saba Sabaratnam]

Other option may be to use the following formula.

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

Kind regards

Saba

 

[Peter_SSs]

Other option may be to use the following formula.

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

Same comment ..

Unless the inserted (or deleted) row(s) are at the top of the range?

 

[StephenCrump]

Unless the inserted (or deleted) row(s) are at the top of the range?

True.

But based on the original post, I assumed data starts in row 2, probably with a header in row 1. So if you insert 10 rows above row 1, the start row for the sum should adjust to 12. That's not what the OP's INDIRECT("I2 ... ) would have done, of course, but I suspect it was the intention.

It looks like Saba interpreted the same way.

 

[Peter_SSs]

But based on the original post, I assumed data starts in row 2, probably with a header in row 1. So if you insert 10 rows above row 1

You are probably right about the OP inserting rows at the bottom, but I was thinking that if headings in row 1, a new row may inserted at row 2 that needed to be picked up by the formula. In that case your method but using I$1 might be robust enough. Similar for Suba's

 

[bchez]

You could try this instead. The I14 in this formula is the address of the cell that the formula is actually in. When copied to other cells or new rows inserted it will adjust accordingly.
I assume that you are aware that this and your original suggestion are volatile functions?

=SUM(OFFSET(I14,-1,,1-ROW()))

You could try this instead. The I14 in this formula is the address of the cell that the formula is actually in. When copied to other cells or new rows inserted it will adjust accordingly.
I assume that you are aware that this and your original suggestion are volatile functions?

=SUM(OFFSET(I14,-1,,1-ROW()))

 

[bchez]

Hi All - I needed to give you a bit more info. Peter and Saba - both your formulas work when I put them into the Week 1 East totals cell. But when I copy it over to the Week 2 East totals cell, it adds the 20 for Joe Smith, who works in the West. What I'm going for is say I have to add another row for a new person who works in the East. I would add this person below Dave Thomas and I want the formula to add the new person's hours in but not add the 16 hours of Joe Smith, who works in the West. Both formulas do this, but when I copy to week 2 the formulas add everything in that column. Stephen I could not get the CellAbove to work but it's my error, I didn't provide you all with the correct description. I actually have 5 regions, so 5 different totals needed in the same column and the ability to add rows to each region and have the formulas work. Thank you all - your knowledge is amazing!

Name	Week 1	Week 2
Joe Smith - West	16	20
West Totals	Formula to add just West totals here	Formula to add just West totals here
Alan Barnes - East	40	40
Dave Thomas - East	32	10
East Totals	Formula to add just East totals here	Formula to add just East totals here