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?
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 6.0.7.3.
That worked! Thank you! Can you explain to me why the number 9 was needed in the equation?
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)
Oh, cool! I didn’t know that!
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)…