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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-10-06T12:56:00+00:00

    same issue 

    creating result of class 

    i have used sum formula ,selecting range A1,C1,G1 etc using ctrl.

    value 99 100 100 respectivly 

    but result is 298 instead of 299 .

    first row result is correct .on dragging other row result is one value less (as discussed above )

    kindly help to resolve same prob

    Was this answer helpful?

    0 comments
  2. Anonymous
    2018-08-14T09:26:11+00:00

    Another option, which I've found to be more versatile: the value() function. It can usually be added without much effort, and without side effects.

    example: change from '7' to '=value(7)'

    Was this answer helpful?

    0 comments
  3. Anonymous
    2018-08-10T01:09:38+00:00

    As much as I know, the Calculation feature ("Automatic/Manually") added from Excel 2007. The default calculation mode is Automatic. You can set the default Calculation mode Manual from File | Options | Formulas.

    This feature is very useful because it's very disturbing to work with large number of data (Around 500000) with formulas. If you change any cell then it takes time to update all 500000 rows. And it takes long time which is unexpected in my working hour.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2018-05-08T15:00:18+00:00

    If multiplying by 1 is a remedy, the data type of the "numbers" is text.  You must use ISTEXT to discern that.  The cell format and the visual appearance are misleading.

    But it is possible that the cell format is also Text -- or it was Text when you entered the data.  Simply changing the cell format to General or another numeric format later is not sufficient.  You must also "re-enter" the data:  select the cell and press f2, then Enter.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2018-04-22T08:29:01+00:00

    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.

    Was this answer helpful?

    0 comments