Ask Your Question

Can I Set an Equation to Automatically Change?

asked 2019-08-08 17:21:44 +0100

LeoLeoness gravatar image

updated 2020-07-11 21:24:31 +0100

Alex Kemp gravatar image

I have a column of numbers, and at the end of the column I have a simple SUM equation [=SUM(C3:C13)]. Each day, I add another row to that column, but to update the sum I need to change the equation from C13 to C14 manually. Is there a way to do this automatically?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-08-08 22:50:00 +0100

gregors15 gravatar image

Hi, Try using =subtotal(9,C3:C1000) instead of =sum. Then each day "insert line below" and the total will automatically update. The C1000 will need to be changed when you get to row 1000. Let us know what you think. I am using windows10 build 1903 and LO

edit flag offensive delete link more


That worked! Thank you! Can you explain to me why the number 9 was needed in the equation?

LeoLeoness gravatar imageLeoLeoness ( 2019-08-08 23:06:09 +0100 )edit

Search SUBTOTAL in the HELP; in brief, the first argument defines the function to use in the SUBTOTAL, e.g. AVERAGE, SUM etc.

Of course you could equally use =SUM(C3:C1000)

robleyd gravatar imagerobleyd ( 2019-08-09 10:29:31 +0100 )edit

Oh, cool! I didn't know that!

LeoLeoness gravatar imageLeoLeoness ( 2019-08-09 17:24:46 +0100 )edit

Of course you could equally use =SUM(C3:C1000)

No, when the formula is in C14 - then it will be circular reference.

Actually, it will not "need to be changed when you get to row 1000", because inserting rows would automatically change the end of range... so if you currently have a range of C3:C13 to sum, and your formula is in C14, then it's enough to use range C3:C14 in the formula as argument to SUBTOTAL.

If you like to use SUM instead, then you might simply use a separating row between your range and the formula, and have SUM reference this separating row (and you will need to add rows below the last used row in range, above the separating row)...

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-09 18:10:23 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-08-08 17:21:44 +0100

Seen: 121 times

Last updated: Aug 08 '19