To sum cells a2:a5 using sum function. If you insert a row directly above the SUM formula, the new row may not be included in the SUM. It may continue to sum cells A2:A5, and ignore A6. To ensure that new rows are included in the total, you can use the OFFSET function.

Select cell A6.

Enter the following formula:

=SUM(A2:OFFSET(A6,-1,0))

Press the Enter key to complete the entry.

Insert a row above row 6

Type a number in cell A6, and it will be included in the total in cell A7

实际工作中,经常需要在原有求和公式前再增加新行,如果增加一行或增加多行后,每行都输入数据,excel会智能的更改SUM公式的求和范围,如果增加多行后,未按序输入,SUM公式就会保持原来的求和范围以至得出错误的结果，为避免这种现象,我们可以试用offset函数配合sum函数求解,做到以不变应万变。

[A6=SUM(A2:OFFSET(A6,-1,0)) ]