The problem we’re trying to solve is that Excel has stopped making its “sum” button work. As a result, people are left unable to total up the values on a worksheet and will either be forced to save and do this manually or use some other tool that may not be suited for the task. This can become particularly problematic when dealing with large tons of data.
What Causes This Error and How to Fix It
The cause of this depends entirely on what you’re doing to trigger the error, but the general solution is pretty consistent across all those scenarios.
What you probably didn’t know is that there are two different ways of calculating a total on a spreadsheet. The first is by selecting all the cells you want to summarize and pressing “control + tab + L” or “control + shift + L“, which triggers the “totals” option under the “review” tab in Excel.
This, however, doesn’t work at all if you’ve applied a filter on any of the columns and since Excel has decided to halt the work of this particular button, that’s precisely what it’s refusing to do.
So, be sure to uncheck all the filters in order to have a clear view of your data, and then you can proceed with the buttons as usual. The error message might go away by itself after that, but if it doesn’t, simply close and open Excel again.
A related problem is that Excel might also refuse to sum up the numbers after clicking on a hyperlink in the results of a filtered search. Clicking on a link without clearing the filters is the easiest way to avoid this.
The Bottom Line
There are plenty of things that can cause you to lose the ability to find out the value of any cell you want in Excel, but this mistake is entirely preventable and easy to solve.
For the most part, it’s easy to go back and give the sum button a second chance when you’re in a pinch, assuming Excel has actually stopped working with it. If not, though, then at least you won’t be wasting time looking for a workaround that doesn’t exist. You can know about Show Empty Cells As Gaps Not Working and fix the problem that you are facing in excel.
Sum Not Working In Excel Pivot Table
Excel’s Pivot Table has its own set of features that are quite distinct from the usual spreadsheet, which means that you might have to outsource the sum function to another one.
It’s pretty simple: all you have to do is go to add a new column and select “sum” from the drop-down menu. That’s all you have to do. If you cannot find the “sum” button, then check to make sure it’s turned on.
If you’re not willing to move away from the current tool, though, then there are plenty of options to choose from. Just like with any other task in Excel, there’s a simple way of doing things and a more complicated one.
If you’re willing to stick with the native tools, then simply right-click on any cell you want to add up and select the “sum” option from the menu. If you’re more comfortable dealing with cells by their name rather than their position in a column, then you can also use cell references instead of row or column names. Just make sure that the two cells have already been selected before entering the formula.
Now, the simple way of doing things is to use a function that lets you instantly sum up any cell or range. The way to do it is by selecting the cell or range you want to total and then entering =sum(cell1,cell2…) as its formula.
While it seems pretty easy to do so, you might be mixing things up with the range name and cell references, so it’s best to double-check everything.
Why Is My Sum In Excel Showing #
This is yet another problem that can cause the total to fail on your spreadsheet, but you’ll have to know a little bit about how Excel works in order to fix it.
The most likely reason for this is that the cell you’re trying, to sum up, is formatted as a currency, but there could be other causes as well. If you’re dealing with numbers and you can’t find anything that seems out of place, then try the other solutions below.
Read More: Excel Wrap Text Not Working?
What’s Causing This Issue and How to Fix It
In this case, the problem is that Excel has decided to “hide” some of the digits in your cell value. A lot of people don’t realize that any number that’s formatted as a currency in Excel has to include all the digits, but it does.
In addition to causing the sum value to be “missing”, you might also have problems with text or number formatting as well, but at least you’ll be able to see that your cell contains numeric data. If everything seems fine, then it means there’s something else going on and you’ll need to do some further troubleshooting.
If you’re sure the problem is with the format of your cell and you’re using a currency symbol, then it’s likely that Excel has just “blocked” access to some of the values contained in your sheet. The exact procedure depends entirely on what prompted you to use a currency in the first place, but there are some things you can try first.
The Bottom Line
At this point, there are no special tricks or shortcuts involved in fixing this issue. Your cell only needs to be formatted.
For starters, if you’re dealing with a currency, then there’s not much to do except select the cell with the total and pick a different format instead of a currency. It’s usually best to just go with “general” or “number” unless you’re planning on reusing this value again in the future.
The same steps will apply if you do not see a currency format. Make sure that each of the numbers contributing to your total is formatted as a number and double-check if you’re missing some digits in any of them.
Once you’ve corrected any format issues there might be, try typing the formula again just in case Excel is only refusing to accept it for some reason. In all likelihood, though, the issue will be resolved by now.
Final Word
The Excel Sum function will give you an accurate sum of the numbers in one, two, or three columns and rows. Space your columns correctly if you want to add up more than one column. To correctly space your columns place a number in the first column, a letter in the second column (A=1), and a number in the third column (B=2).
Once done select your entire spreadsheet with CTRL + A and copy everything with CTRL + C. Next, open the Page Layout tab of Excel and select the Spacing option. Once done you will need to adjust each of your columns by dragging the edge lines until they are exactly equal to one another.