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

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.

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

Next>Next>General>OK

Gord

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!

I had the same problem, go to cell and change type of entry

• mine was entered as text - so I had to change it to a number entry

(even though I entered numbers) - weird yes!

Anyway that worked for me - after much scratching my head and reading responses here, slowly I pieced it together - note, I am no expert.

This is still a problem for me in Excel 2013, after trying all of these suggestions. It turns out the cells not being added were those which were constants (like 7 or =7), instead of calculations (like =A2*2).

WORKAROUND SOLUTION that works for me: Change what I referred to as 'constants' [I know that's probably the wrong word] to formulas multiplied by 1. For example, change '7' to '=1*7'. Then it'll be added in the sum formula.

Very strange!