Auto sum of value when adding new row

[choy96]

I looking for a good formula that can be total a value when add more row into it. i have try subtotal(9,B5:B8) but this formula does not work, always need to click "Update formula to include cells", very troublesome. Have any idea ?

[martindwilson]

just use =sum(b5:b9) this will change to =sum(b5:b10) when you add a row

[ChemistB]

Here's one solution. In B10

=SUM($B$5:OFFSET($B$10,-1,))

This assumes you're not inserting in front of B5. If you are, then

=SUM(INDIRECT("B5"):OFFSET($B$10,-1,))

[choy96]

thanks, what happen if i unlock the B10 on "=SUM(INDIRECT("B5"):OFFSET($B$10,-1,))" because i need draw the formula to the right.

[ChemistB]

It should not be a problem.