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
    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
  2. Anonymous
    2018-08-14T05:48:18+00:00

    Mate, I never knew this feature existed and certainly never changed it from the default to manual. One day a couple of weeks ago, my spreadsheets started to behave differently. First time I have seen this in 30 years of working with spreadsheets. I'm assuming Microsoft made an update to Office 365 and changed the default to manual. No other way it could have happened.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. 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
  4. Anonymous
    2018-08-16T15:07:49+00:00

    This worked!  My column was not summing to the correct sum....I went to the Formula Tab>Error Checking>Circular Reference.  This showed me where my circular reference was....Once I fixed this the column was summed correctly.

    Thanks!

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  5. 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