Read More: Why Formula Is Not Working in Excel (15 Reasons with Solutions) Click as follows to set Automatic mode: Formulas ➤ Calculation Options ➤ Automatic.But it doesn’t happen in the latest version- Excel 365, which can happen in some earlier versions.Īlways keep the calculation mode to Automatic. If you keep the calculation mode in Manual mode then it might be a reason and for that Excel SUM formula is not working and returns zero. How to Sum a Column in Excel (6 Methods).Formula Not Working and Showing as Text in Excel.: Excel Formulas Not Updating Until Save (6 Possible Solutions).: Excel Formula Not Showing Correct Result (8 Methods).How to Refresh Formulas in Excel (2 Easy Methods).Read More: SUM Formula Not Working in Excel (8 Reasons with Solutions) Then use the SUM formula and you will get the expected result.Next, use the Fill Handle tool to copy the formula.Then write the following formula in Cell D5–.We’ll use the VALUE function in this regard. Lastly, I’ll show how to use the function to solve the problem. After appearing the Paste Special dialog box, mark All from the Paste section and Add from the Operation section.Then select Paste Special from the Context menu.Next, select the cells and right-click your mouse.It may be helpful in some particular cases. Now let’s use a tricky way- Paste Special to convert Text into Number. Read More: Sum to End of a Column in Excel (8 Handy Methods) Then you will get the proper output from the SUM formula as it should be. Soon after a dialog box with 3 steps will open up.Then click as follows: Data ➤ Data Tools ➤ Text to Columns.Now see, we got the values as numbers and the SUM formula worked properly.Īnother useful solution is to use the Text to Columns Wizard. Later, select Convert to Number from the Context menu.Read More: Sum If a Cell Contains Text in Excel (6 Suitable Formulas)įirst, I’ll use the Convert to Number command to convert the text values into numbers. So the SUM formula recognized no numbers and that’s why returned zero. That’s why there are green triangular icons in every cell. The reason is that I stored the numbers as text values. Have a look that I have used the SUM function here to find the total quantity but it returned zero. To explore the solutions, we’ll use the following dataset that represents some ordered Smartphones and their Quantity. SUM Formula Is Not Working and Returns 0.xlsxģ Fixes: Excel Sum Formula Is Not Working and Returns 0

Excel is showing formula not result