SUM does not work even though all cells are numeric; however, instead of using"=SUM(a1:a3)" if I use “=a1+a2+a3” I get the correct result. but if there are many cells to add, this is an unreasonable approach
Please upload your ODF (.ods) type sample file here.
Maybe you have set localized function names. For example zhe SUM() = SZUM() in the Hungarian locale settings.
The reason is: the Content of the Cells is NOT numeric ( even it looks like ) , but Text
the Function SUM ignores Text, but the +operator implicitly casts Text into numbers (if possible)
please attach a Sample.ods with False Data.
2 Likes
I tried following the instruction, but my data did not have the “’” or the .dot, so I still do not know how to convert
I assumed this was the problem, but do not know how to fix it. I am attaching the calc file “VLOOKUP w SUM…”; check cells CE283 and CE491. I am using the sum function, but it gives a zero.This file uses VLOOKUP on tab “All”, which using data copied from “Positions…”. The “Positions…” file was previously a “.csv” file. I did convert this csv file to ods before copying to tab “All” in the calc file called “VLOOKUP w SUM…”
Thanks for your help. If I can get this to work, I am planning on converting to Libre Calc from Excel.
karolus
July 12
The reason is: the Content of the Cells is NOT numeric ( even it looks like ) , but Text
the Function SUM ignores Text, but the +operator implicitly casts Text into numbers (if possible)
please attach a Sample.ods with False Data.
Convert your text that looks like numbers to real numeric cell content by following some of this FAQ.
Find & Replace number text
- mark the cell range containing the data to convert
- press Ctrl+M or right click on the marked range to get the context menu and select Clear Direct Formatting
- if your locale does not use the separator(s) that are required for the text to be converted to number then temporarily apply a number format of a locale that uses the desired separator(s), for example English-US to get the . dot decimal separator; you can then later after the conversion apply the desired final number format of your locale. Either that, or replace the decimal separator, skip to the next example further below.
- press Ctrl+H or choose Edit → Find & Replace from the menu
- enter Find: .+
- enter Replace: $0
- under Other options activate
- Current selection only
- Regular expressions
- hit Replace All
This enters the cell content anew, basically as if it was entered from keyboard, and detects numeric/date/time input.
Find & Replace with different decimal separator
If the number wasn’t recognized because your locale uses a different decimal separator than the data, for example , comma instead of data’s . dot (and thus the Input Line does not display the ’ apostrophe prefix), then
- mark the cell range containing the data to convert
- press Ctrl+M or right click on the marked range to get the context menu and select Clear Direct Formatting
- press Ctrl+H or choose Edit → Find & Replace from the menu
- enter Find: .
- enter Replace: ,
- under Other options
- activate Current selection only
- deactivate Regular expressions
- hit Replace All
Note this does of course not work if the numbers contain a mix of group separators and decimal separators and the replacement would yield identical separators.
WHAT DOES ALL THIS MEAN???
Did not really understand all this, but converting the column type to English solved the problem.