Excel "=SUM" formula does not add up numbers correctly

Hi folks,

Just found this article as I was trying to figure out how (like the title says) the SUM function was *not* returning a correct sum for me.  In my case, I had added a cell into an existing range, copy and pasted a numeric value from a webpage into that cell, and was perturbed when the total didn't change.

I didn't see a solution here that specifically worked for me, but all the talk about cell values and value types must have gotten my eyeballs to look a wee bit closer at what I had entered... and I did end up figuring out what happened for me.

Turns out, when I pasted that numeric value into that cell, I didn't actually paste *just* the number - I also left a space after it.  Once I deleted that space and hit enter, the formula immediate summed up correctly.

Ahhhh... <space>.  Apparently SUM *also* abhors a vacuum.  ;-)

Hope this helps someone with their mystery!

A shorter method may have been to run the data through the Text to Columns Wizard.

Next>Next>General>OK

Gord

Your remedy suggests that Gord is right, after all.

You must use ISTEXT or ISNUMBER to determine if a cell value is text (lowercase).  It is not determined by cell format (Text; capitalized). We can have text values in cells that have a non-Text format. And we can have numeric values in cells that are formatted as Text.

Although Excel usually inputs data with a "$" as numeric (at least in regions where that is the currency symbol), there might have been a reason why it didn't in your case, as you suggest.

Nevertheless, you are correct that there are other reasons why SUM to return zero, or at least to appear to be zero. The only way to determine if a cell is truly zero is to format it as Scientific (0.00E+00 is exactly zero) or to enter a formula of the form =A1=0.

Well, not always. I had to remove $ from all the numbers and I got the correct number. I don't know why that affected it, but then I had to download the report, so it may have been something that happened in the download.

You do not offer sufficient detail for a knowledgable explanation of the problem. In fact, even the problem ("incorrect total") is too vague.

If you would like an explanation, I suggest that upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website (e.g. box.net/files) and post the public/share URL in a response. Test the download URL first, being careful to log out of the file-sharing website.

The devil might be in subtle details that might be overlooked or misrepresented.

Justin wrote:

Auto Sum gave me an incorrect total, but if I added manually or manually put the cells into an addition formula they gave me the correct total. I had to re-enter the numbers in the cells and only then did they work in the Auto Sum formula.

That could describe a subtle anomaly that arises with binary computer arithmetic. Or it could describe a lack of understanding of your formulas and the need for explicit rounding.

A common mistake is to think that the displayed value is the actual cell value. For example, if the formula is =123.45/2 formatted to display 2 decimal places, the result might appear to be 61.73, but it is actually 61.725. When you manually use or re-enter 61.73, you "correct" the problem as you see it.

Alternatively, a common anomaly arises because of the type of internal binary representation that Excel uses. As a consequence, for example, =IF(34.56-34=0.56,TRUE) returns FALSE(!).  

Justin wrote:

I made sure to change their formatting from General to Numbers. However, this didn't change anything. [....] So I guess that even if you change a cells format it can decide not to change the format of what was in there previously?

Usually [1], the cell format only changes the appearance of a numeric value; it does not change the actual value.

[1] An exception is when the "Precision as displayed" option is enabled, which is not recommended.  If PAD is set, changing the precision of the cell value can change the cell value and the value of dependent formulas by changing the way that the cell value is rounded.