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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2018-08-01T20:42:56+00:00

    I have Microsoft office 365 - Excel is not adding certain cells correctly

    e.g. - say - =sum(i43:i59) = should be £32000 - Excel is adding it as £43000

    Found that it is including cells i63-i72 - tried deleting these - still ads wrongly

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2018-05-08T17:43:20+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?

    1 person found this answer helpful.
    0 comments
  3. 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
  4. Anonymous
    2018-05-08T14:39:52+00:00

    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!

    Was this answer helpful?

    1 person found 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