Keeping SUM range when adding a new row to the 1st place

[sduttonusa]

I have numbers across rows 10 to 25. Underneath (say row 45), I get the SUM of the columns above =SUM(A10:A25), =SUM(B10:B25), etc. When I add a new row somewhere in the middle of the rows, the bottom of the column SUM takes into account the new row =SUM(A10:A26), =SUM(B10:26), etc.

But if I add a new row in the 1st place position, row 10, the SUM below becomes =SUM(A11:A26), =SUM(B11:B26) . . . row 10 doesn't get included. This even happens if I create a new row in the middle, and then later move it up to the 1st position.

Is there a way around this?

 

[sduttonusa]

Is it OK to use the OFFSET concept at the beginning of the formula? In other words (using the above table), =SUM(OFFSET(B4,+1,):OFFSET(B9,-1,)) This would mean that I want the SUM from B5 to B8 . . . is that correct?

 

[markmzz]

Is it OK to use the OFFSET concept at the beginning of the formula? In other words (using the above table), =SUM(OFFSET(B4,+1,):OFFSET(B9,-1,)) This would mean that I want the SUM from B5 to B8 . . . is that correct?

Yes, in your case, you can use OFFSET function at the beginning of the formula too.

The formula below Works too:

=SUM(OFFSET(B4,1,):OFFSET(B9,-1,))​

Markmzz

 

[sduttonusa]

Thank you!

 

[markmzz]

Thank you!

If you have only text in the row 4, you can try this too:

1) With the cell B9 selected, create the name below

Range - Refers to: Sheet1!B$4:B8

2) After that, use the formula below in B9

=SUM(Range)

Markmzz