Forum Discussion
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
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!
