'Excel SUM function is not working (shows 0), but using Addition (+) works
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:
- SUM(A1:A2) shows 0.0
- A1 + A2 shows 43.2
I don't see any errors or little arrows on any of the cells.
Solution 1:[1]
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.
Solution 2:[2]
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()
Solution 3:[3]
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 "."
Solution 4:[4]
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
Solution 5:[5]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Bryan Williams |
| Solution 2 | asac |
| Solution 3 | IBam |
| Solution 4 | Angelica |
| Solution 5 | Vaughan Pratt |