How to automatically add an extra row and automatically update sum formula

It would be easier and probably safer to put the total somewhere away from the bottom of the column.

Inserting a row automatically requires vba.

Having the total somewhere else (away from column C) only requires a simple formula, =SUM(C:C)

Moving the total to the top of column C (inserting a row above the headers ) could be done with =SUM(C3:INDEX(C:C,MATCH(1e+100,C:C)))

Thanks so much! Can you possibly explain what the 1e+100 means?

It's a big number in scientific notation, in reality it only need to exceed the largest check amount that will ever be entered, so if you never recieve checks for more than 1000, then 10000 would be more than adequate. Using 1e+100 (1 followed by 100 zero's) is a good failsafe to make sure that you never exceed the amount set should there be an extreme exception to the norm.

Originally Posted by jason.b75

It's a big number in scientific notation, in reality it only need to exceed the largest check amount that will ever be entered, so if you never recieve checks for more than 1000, then 10000 would be more than adequate. Using 1e+100 (1 followed by 100 zero's) is a good failsafe to make sure that you never exceed the amount set should there be an extreme exception to the norm.

I really am still confused. When I run the index match on its own it just returns the last rows value. If you could break down the formula fully for the layman it would be super appreciated!

When I run the index match on its own it just returns the last rows value.

That is correct, but in the background INDEX actually returns the address of the cell that contains the value not all functions do this, but INDEX is one that does so we can make use of this.

For instance if your last entry is in C50 then =INDEX(C:C,MATCH(1e+100,C:C)) will result in =C50 rather than what is in C50, but then it shows the result as if you typed =C50 into the cell.

Then if you look at the formula where the INDEX function would evaluate to C50, you will see that the final result will be SUM(C3:C50)

=SUM(C3:INDEX(C:C,MATCH(1e+100,C:C)))