I'm trying to =SUM(D2:D600) but it seems after some testing it can't deal with ",00 / ,xx" at the end of numbers. Is the .csv unusable when formatted like this? Is there a setting to fix it?
(I originally thought it was thrown off by the empty rows between the cells with data, but I made a little mockup test and it's clearly adding the ,00 at the end that makes Sheets read 745,00 as 0 when just 745 alone worked fine.)
Posted Dec 17, 2023, 4:17 PM UTC
Likely what's happening is that you're trying to use commas as decimal separators while your file is set to a region that expects periods as decimal separators. You will need to either input your data as 745.00 or change your file to a region that accepts commas as decimal separators (File > Settings > Locale). You can see the regions that use period-decimal syntax and comma decimal syntax here.
Hmm, made the change and saved the setting and it still didn't work.
Screenshot: https://i.imgur.com/CtCG8Pe.png
Your cells are still formatting as text somehow. Make sure their format is set to "Number" or "Automatic." If that still doesn't work you'll need to reproduce the error on a sample sheet and share it here (with edit permissions enabled) since continued problems are indicative of an issue with something specific to your file and/or input.
Solution Verified
You have awarded 1 point to HolyBonobos
^(I am a bot - please contact the mods with any questions. | ) ^(Keep me alive)
Thank you, I selected the D column from 2 to 600, clicked "Format" in the top row, hovered "Number" and clicked "Automatic" from the Number menu.
Now the numbers all moved to the right side of their respective cells, and my SUM cell seems to display the correct sum.
REMEMBER: If your problem has been solved, please reply directly to the author of the comment you found the most helpful with the words "Solution Verified" which will automatically mark the thread "Solved" and award a point to the solution author as required by our subreddit rules (see rule #6: Clippy Points).
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
need to change your region to a region that uses that in the settings.
I mentioned it to the other comment but I made the change and saved the setting and apparently it still didn't work.
Screenshot: https://i.imgur.com/CtCG8Pe.png
Well then your cell values have trailing white space, or your cells are formatted as text.
Sheets handels
,00just fine, so its your data that's the issue.