Re: [libreoffice-users] How to get column total in last row in calc?

At 08:44 31/03/2026 +0100, Chris Green wrote:
> If I have a spreadsheet with a column containing numbers how do I 
> get the sum of all the numbers in the column and put it in the last 
> row? Yes, I know the obvious answer, if it's column B and I have ten 
> rows in my spreadsheet then the answer is:-
>
> SUM(B1:B10)
>
> However this would need to be changed every time I add a row to the 
> spreadsheet. There is surely a way of specifying 'the row before 
> this one' as the second parameter [...]. So, how do I specify 'the 
> row before this one'?

At 11:12 31/03/2026 +0200, Neil Roberts wrote:
> I don't know if there's a simpler way, but one way could be to use 
> the ROW() function to get the current row, the ADDRESS() function to 
> convert the row calculation back to a cell reference and then the 
> INDIRECT() function to get the value of the cell reference.
>
> So to sum all of the numbers above the current cell you could use 
> this formula:
> SUM(INDIRECT(CONCAT(ADDRESS(1;COLUMN());":";ADDRESS(ROW()-1;COLUMN()))))

Whilst this formula could be useful in general circumstances, there 
is indeed a somewhat simpler way (which doesn't need the ADDRESS() 
function at all) for a particular case. To sum as requested from B1 
to the previous row in column B, you could use

=SUM(INDIRECT("B1:B"&ROW()-1))

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy