SOLVED

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

Copper Contributor

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

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 (Copper Contributor)
Solution

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

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

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

Thank you so much!

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

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

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

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!

1 best response

Accepted Solutions
best response confirmed by S-C-K (Copper Contributor)
Solution

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

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