I'm stumped in Excel (version 16.0, Office 365). I have some cells that are formatted as Number, all with values > 0, but when I use the standard SUM() on them, it always shows a result of 0.0 instead of the correct sum. When I use + instead, the sum shows correctly.
For example:
I don't see any errors or little arrows on any of the cells.
Excel is telling you (in an obscure fashion) that the values in A1 and A2 are Text.
The SUM() function ignores text values and returns zero. A direct addition formula converts each value from text to number before adding them up.
Using NUMBERVALUE() on each cell fixed it. Even though each cell was formatted as a Number, since the data was originally extracted from text, the cell contents apparently were NOT being treated as a Number. Yet another flaw in Excel.
I get a similar issue while importing from a csv.
Selecting the cell range and formatting as number did not help
Selected the cell range then under:
Data -> Data Tools -> Text to Columns -> next -> next -> finish
did the job and numbers are now turned into numbers that excel consider as numbers !
This avoids use of NUMBERVALUE()
There is a much faster way you just need to replace all the commas by points. Do control-F, go to "Replace" tab, in "Find what" put "," and in "Replace with" field put "."
I noticed that if the sum contains formulas if you have any issue with circular references they won't work, go to Formulas-->Error checking-->Circular References and fix them
If there's a tab in your .csv then Excel will interpret it as text rather than as white space. A number next to a tab will then be seen as text and not as a number. Convert your tabs to spaces with a text editor before letting Excel at it.
