jnelsoninjax Posted January 2, 2022 Share Posted January 2, 2022 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? Link to comment Share on other sites More sharing options...
+Jester124 Subscriber² Posted January 2, 2022 Subscriber² Share Posted January 2, 2022 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. Link to comment Share on other sites More sharing options...
jnelsoninjax Posted January 2, 2022 Author Share Posted January 2, 2022 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 1 Share Link to comment Share on other sites More sharing options...
+Jester124 Subscriber² Posted January 2, 2022 Subscriber² Share Posted January 2, 2022 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? Link to comment Share on other sites More sharing options...
jnelsoninjax Posted January 2, 2022 Author Share Posted January 2, 2022 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? Link to comment Share on other sites More sharing options...
Gerowen Posted January 2, 2022 Share Posted January 2, 2022 (edited) 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 Link to comment Share on other sites More sharing options...
+Jester124 Subscriber² Posted January 2, 2022 Subscriber² Share Posted January 2, 2022 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 Link to comment Share on other sites More sharing options...
jnelsoninjax Posted January 2, 2022 Author Share Posted January 2, 2022 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 1 Share Link to comment Share on other sites More sharing options...
+Jester124 Subscriber² Posted January 2, 2022 Subscriber² Share Posted January 2, 2022 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 and jnelsoninjax 2 Share Link to comment Share on other sites More sharing options...
Gerowen Posted January 3, 2022 Share Posted January 3, 2022 (edited) 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now