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

Anonymous
2013-04-02T16:39:01+00:00

I have an Excel formula issue in the formula not resulting in the correct sum, but it is not a rounding error; rather it is off by an entire cell amount. As an example, I was adding eight cells with the value of $3,001.53 which should have resulted in a total of $24,012.24, but instead I got $21,010.71 (off by one cell value). I triple-checked to make sure that the formula included all cell addresses I was trying to add, which it did. I tried deleting the formula and doing it over again, saving the spread sheet as different versions of Excel, putting the formula in a different cell location, all to no avail. There were other similar errors in the spread sheet. FYI, I am not adding concecutive cells, rather, selecting certain cells using Ctrl, left click.

I'm using Excel 2010 Version 14.1.6129.5000 (32-bit).

Please advise.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

43 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2016-03-01T19:03:22+00:00

    I had the same problem in Excel 2013; just about pulled out my hair. I discovered that, although I had selected cells and formatted them as Number, Excel still had them as General so they were not being counted in the SUM formula. There was a tiny yellow diamond with ! inside next to each one. When I hovered over the diamond and clicked, I was given a menu from which I chose "convert to a number". Problem solved.

    Was this answer helpful?

    20+ people found this answer helpful.
    0 comments
  2. Anonymous
    2016-03-01T20:08:21+00:00

    For each of those cells that make up the sum, can you select each one at a time and look in the formula bar to see that they actually have 3,001.53 as their value?

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2016-03-01T20:18:10+00:00

    Marsha wrote:

    I discovered that, although I had selected cells and formatted them as Number, Excel still had them as General so they were not being counted in the SUM formula. There was a tiny yellow diamond with ! inside next to each one. When I hovered over the diamond and clicked, I was given a menu from which I chose "convert to a number". Problem solved.

    The problem was not the cell format; any numeric cell format will do, including General.

    The problem was:  the cell value was text, not numeric.  That is the type of the value, not the cell format.

    How that happened is anyone's guess.  You offer no clues.  Most commonly, it was the result of copy-and-paste.  Sometimes it is the result of importing from another application.

    We can enter text into any cell, regardless of the cell format.  In particular, the cell format does not have to be General or Text.

    PS....  Simply changing the cell format is not sufficient to convert the cell value from text to numeric.  We must also "re-enter" the cell value.  That can be done after changing the cell format by selecting each cell and pressing F2, then Enter.  But of course, that is tedious if you have a lot of cells to convert.  There are alternatives, like the one you discovered.  But they all run the risk of changing the cell value infinitesimally in some situations.

    Was this answer helpful?

    20+ people found this answer helpful.
    0 comments
  4. Anonymous
    2016-03-02T04:13:22+00:00

    Absolutely. This is very worrying. I now have to check all sums or additions very carefully and have no idea how much this flaw in Excel has affected work, except presume that it commenced with 2013 version and onwards

    Once the cell is found it has to be re-entered F2  etc as described by others.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  5. Anonymous
    2016-03-19T17:01:53+00:00

    2007 has the exact same issue/flaw/bug. It's happening to me when I copy some cells into a table. Doing F2 enter on the cells I'm copying from, fixes the problem on future copy and pastes.

    It sure would would be nice if someone from Microsoft was to read this forum and fix it.

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments