This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
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.
Hi. When you select the 8 cells, take a look at the lower right corner of the status bar line.
What does the 'Count and 'Sum say?
(I just turned on 'Numerical Count' with Excel 2013. I honestly don't remember if this was an option in Excel 2010. I've had Excel 2013 now for only a week :>~ )
<removed>
I have been using Excel for almost twenty years, as a construction estimator I use Excel daily and I depend on Excel for income. If Excel is not functioning correctly I lose income.
Using Excel 2016. This same error, where cells are SUM to zero instead of the actual sum occurred in Excel internally. I did nothing to cause it. It started when I noticed my SUM cell was not changing. I tried to fix the cell and now I get Zero. Tried new cells, new locations and the same failed results.
When selecting cell, the SUM in the lower right is correct. But the AutoSum still will not work. I have forty tabs with forty estimates and all their sums link to a summary tab. I can’t afford this. I don’t have the time; my bid is due. I require Excel to function correctly.
Have checked: Cells are formatted correctly, no hidden rows (bad idea with estimates), File>Options>Formulas>Workbook Calculation>Automatic was checked, and there is not tiny yellow diamond.
Microsoft needs to fix this.
In my experience any numbers that add up to 0 are those that have been formatted as Text in most cases.
Gord
All of a sudden, I find that spreadsheets I have been using for over a decade no longer calculate simple math correctly. Simple math! I have a column of numbers that I add up using =sum(..) function, which has been the same for decades. i started noticing bogus output a few weeks ago in one spreadsheet and have been scratching my head over it. Now, when I change the numbers in the column being added, the output cell remains the same! WTF???? I thought I had a buggy spreadsheet or maybe a virus. Each time I wanted to do the calculation, I had to clear the output cell and reenter the function. Today, I saw the same behavior in another spreadsheet that I have used for years and years. So I called Microsoft. Those idiots batted me around between support "professionals" for a long while and I did some searching while I was on hold until I found your post, checked the settings, and surprise surprise surpise - you are correct. It was set to "manual" instead of "automatic"! But the question is, when was this "feature" added, and the bigger question is who the f would ever think of such a stupid idea and set it as the default without warning the entire world that their spreadsheets would no longer perform basic math???? my God. I am at a loss. A loss! How many errors have I been putting out to my business partners with my spreadsheet output and for how f-ing long? I simply cannot imagine any purpose for setting it to "manual". That is why we use spreadsheets! To quickly perform mathematical functions. If I wanted to do the work manually, I'd use a goddam adding machine for christsakes. What idiot though of this and when did Microsoft(brains) foist this upon us? I certainly never changed this setting in Excel and never heard of it before, nor would I ever think there was a want or a need out there to manually calculate data in a spreadsheet! Has the entire world been taken over by idiots? Has Microsoft been infected by morons? Or have I been unknowingly transported into a parallel dimension of idiots when before I lived in one where a) no one with even a high school education would even think of such a "feature" and b) Donald Trump was just a bankrupt failed businessman, not president of the US. Please, please tell me how I can get back to the other dimension where at least these two elements of my new reality do not exist. Please!
I had this problem too. Go to Options menu under the File Tab.
Under Excel options there should be one that says Formulas and under that/beside that Calculation Options and Workbook Calculation. Make sure Automatic is checked.
Wow! What a life saver. Thanks.