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-10-06T13:37:53+00:00

    This may or may not be the "same issue" and "same problem".  You are posting to a 5-year-old thread.  It would behoove you to start a new question.

    Since the sum is off by only one, I suspect that one or more of the values are not as they appear.

    Temporarily format each cell with 13 decimal places (to display 15 significant digits in A1).  If you do not see the problem with your assumptions, post the reformatted numbers (15 and 16 digits).

    It is unclear to me what you mean by "first row result is correct. on dragging other row, result is one value less".

    Presumably, A1, C1, G1 comprise the "first row", and you say their sum is not correct.  If you still do not see your mistake after temporarily reformatting, please clarify your statement and procedure about "dragging".

    Was this answer helpful?

    0 comments
  2. Anonymous
    2018-12-18T18:44:00+00:00

    I quite agree with you!  All of the sudden. My methodologies have not changed, and yet some setting somewhere seems to determine that an identical list of #s in one column sums to a different value in its neighbour.  I have copied/pasted values only and still the thing adds differently.  I am finding that efficient things such as copying and pasting #'s or asking it to sum in a particular direction gives me "table" type formula instead of showing me what it is adding.  I don't like it.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-02-04T21:45:05+00:00

    I know this is an old thread, but the problem remains nonetheless; and another quick solution seems warranted.

    One thing to check for is hidden values (blank/string) that precede the numeric value.  Hidden values such as tabs, spaces, and other "clear" characters might not be visually evident, but could still exist.  Those would force Excel to see the cell as text, regardless of whether you change it to general or numeric.

    Click within the cell, and go all the way to the left and right.  You might be shocked by some hidden values.  These probably came from copy/paste between formats/encodings

    To resolve, use the "replace all" function to strip out any instances you find, such as spaces or tabs etc.

    Worked for me, after pulling out my hair after changing to General or Numeric didn't work.

    Was this answer helpful?

    0 comments