AutoSum of a column is not updating when a row is added above it. Suggestions?

New Contributor

How can I get autosum of a column to update automatically when a new row is added above the autosum?


4 Replies
best response confirmed by S-C-K (New Contributor)


Format your data as Table > Activate the Total Row for that Table > For each column where you expect a SUM, on the total row: SUBTOTAL(109, [<ColumnName>])


Sample attached

You're welcome (Tables bring many other benefits...) & Thanks for providing feedback


You can use the OFFSET and COUNTA functions to create a formula that will automatically adjust when new rows are added within the range.

The formula  =SUM(A2:OFFSET(A2,COUNTA(A:A)-1,0)) will sum the values in column A starting from row 2 and extending down to the last non-empty cell in column A.


The OFFSET function returns a reference to a range that is a specified number of rows and columns from a reference cell. In this case, the reference cell is A2 and the number of rows is calculated using the COUNTA function, which counts the number of non-empty cells in column A. The “-1” at the end adjusts for the header row.


Sample file is attached.


I hope this helps!