Spreadsheet help (LibreOffice Calc)

[jnelsoninjax]

I have a spreadsheet with one column of just dollar amounts, and I simply want to total it, but when I use the SUM function, it comes back as 0 despite how I input it. I am using LibreOffice Calc, since I have no desire to pay for MS Office, can anyone help me out?

[+Jester124 Subscriber²]

SUM should still work with money formatted cells as long as you are not adding the "$" manually as that would make it a string instead of a number value.

As a side note, may want to update the title from Excel help to Calc Help or LibreOffice Calc help.

[jnelsoninjax]

On 02/01/2022 at 16:02, Jester124 said:

SUM should still work with money formatted cells as long as you are not adding the "$" manually as that would make it a string instead of a number value.

As a side note, may want to update the title from Excel help to Calc Help or LibreOffice Calc help.

I changed the title, you were right about that. The formula I used is =SUM(A1:A165) since there are 165 lines but it still displays 0

[+Jester124 Subscriber²]

On 02/01/2022 at 15:16, jnelsoninjax said:

I changed the title, you were right about that. The formula I used is =SUM(A1:A165) since there are 165 lines but it still displays 0

That is very strange. That is the correct format for SUM and it is weird that you are showing 0 instead. To be safe, remove all formatting on the cells and double check to make sure all values would be numbers. Possibly one may be incorrectly entered?

[jnelsoninjax]

On 02/01/2022 at 16:23, Jester124 said:

That is very strange. That is the correct format for SUM and it is weird that you are showing 0 instead. To be safe, remove all formatting on the cells and double check to make sure all values would be numbers. Possibly one may be incorrectly entered?

The numbers are copied from another spreadsheet that was in read-only mode, but I don't see anything in the cell except $ and then numbers. I copied the numbers from it and put it on pastebin: https://pastebin.com/GS56KxVC can you try and see if you can get a total?

[Gerowen]

Last time I did that in LibreOffice it didn't support totaling an entire column infinitely, so you have to specify a range that you know will include all the cells you want.

 

So for example if your desired values are in column B and won't go past 100 rows, you could use this as the formula:

=SUM(B1:B100)

Edited January 2, 2022 by Gerowen

[+Jester124 Subscriber²]

The data showed both having "$" and "," and were being treated as strings in mine. I removed those and re-pasted the data and it worked.
 

Thy to copy and paste the info in this:
https://pastebin.com/PYPh81Mw

[jnelsoninjax]

On 02/01/2022 at 16:41, Gerowen said:

Last time I did that in LibreOffice it didn't support totaling an entire column infinitely, so you have to specify a range that you know will include all the cells you want.

 

So for example if your desired values are in column B and won't go past 100 rows, you could use this as the formula:

=SUM(B1:B100)

That was most likely the issue. But it doesn't seem to be, no mater what I limit it to, it still gives me Err:509, but when I take the data and put it in a new column it works, so IDK!

On 02/01/2022 at 18:19, Jester124 said:

The data showed both having "$" and "," and were being treated as strings in mine. I removed those and re-pasted the data and it worked.
 

Thy to copy and paste the info in this:
https://pastebin.com/PYPh81Mw

I took the text and put it a new column and then the formula worked, thanks!

[+Jester124 Subscriber²]

No prob, glad it is working. Easiest way to get that kind of data formatted the way calc can handle it is just make sure all "$"'s and ","'s are removed. The SUM function expects raw numbers with only the "-" for negative and the "." for decimal work.

[Gerowen]

  

On 02/01/2022 at 18:37, jnelsoninjax said:

That was most likely the issue. But it doesn't seem to be, no mater what I limit it to, it still gives me Err:509, but when I take the data and put it in a new column it works, so IDK!

I took the text and put it a new column and then the formula worked, thanks!

 

On 02/01/2022 at 18:43, Jester124 said:

No prob, glad it is working. Easiest way to get that kind of data formatted the way calc can handle it is just make sure all "$"'s and ","'s are removed. The SUM function expects raw numbers with only the "-" for negative and the "." for decimal work.

Yep.  Any time I'm working with currencies I just enter them as decimal number values, then "format" the cells to the proper currency format so things like $ are displayed, but they're not actually a part of the string/number value of the cell itself.