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

[S-C-K]

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

[#2 · Accepted solution]

@S-C-K 

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

[#3]

Thank you so much!

[#4]

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

[#5]

@S-C-K 

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!