SUM function to handle inserted rows and columns

When I insert a row between the data and the SUM function Excel will usually amend the SUM function to include the new row. Sometimes, however, it doesn’t do it. Is there a way to automatically include the row above the cell with the SUM function?

By Neale Blackwood
When I insert a row between the data and the SUM function Excel will usually amend the SUM function to include the new row. Sometimes, however, it doesn’t do it. Is there a way to automatically include the row above the cell with the SUM function?

Yes, you can use the OFFSET function to achieve this. Assuming the data is in the range A1 to C10. The following formulae should achieve the results you require.

To SUM down in cell A11 enter =SUM(A1:OFFSET(A11,-1,0))

To SUM across in cell D1 enter =SUM(A1:OFFSET(D1,0-1))

Note: Both these formulae use their formula’s cell reference in the formula.

They don’t cause a circular reference because when calculated the formulae don’t’ include their cells in the SUM.