'Excel - float number cannot be summed
I have a bunch of values like 0.5, 1.0, 1.5 etc.
I want to write a SUM function for those cells but the value is 0.
If I use integers the summing works so there is something with the nbrs that is wrong. Maybe they are strings? Because they end up to the left in the cell instead of the right like integers.
What do I need to do?
=SUM(H22:H26)
Solution 1:[1]
In a new column, try multiplying the original column by 1; then sum the new column. Not sure, but that may fix the issue.
Solution 2:[2]
Convert the numbers to values first with
=value(H22), =value(H23), etc.
in column G and then do
=SUM(G22:G26)
Alternatively, you can just do
{=SUM(VALUE(H22:H26))}
Notice this is an array formula, so select it and hit F2 and then CTRL-SHIFT-ENTER
Solution 3:[3]
Especially if the data came from a web page or HTML document, try the following
This formula must be array-entered:
=SUM(IFERROR(--TRIM(SUBSTITUTE(A1:A100,CHAR(160),"")),0))
To array-enter a formula, after entering the formula into the cell or formula bar, hold down < ctrl >< shift > while hitting < enter >. If you did this correctly, Excel will place braces {...} around the formula.
HTML frequently adds the NBSP character for spacing, and that must be removed specifically before Excel will see the value as a number.
Solution 4:[4]
Fell on the issue today: I exported data that I pasted into Excel. The formula =SUM(B:B) supposed to sum my decimals kept on returning 0...
It's stupid, but it made me lose 5 minutes: the decimal separator . had to be replaced by , or Excel wouldn't understand it as a decimal number.
Of course, it probably depends of Excel regional settings.
Solution 5:[5]
If you want to do this in one cell, basically you should convert values and sum them
=VALUE(SUM(H22:H26))
Tried and verified!
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 | JJFord3 |
| Solution 2 | |
| Solution 3 | Ron Rosenfeld |
| Solution 4 | arvymetal |
| Solution 5 | bkmete99 |