How to Fix Excel Formulas Not Adding Numbers Correctly
Question details
The user needs to troubleshoot why an addition formula is producing an unexpected or incorrect total.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Adding a range of numeric values using a formula (like SUM) to calculate a total.
- Observed behavior
- The formula produces an incorrect total, either by ignoring certain cells completely or by calculating the underlying unrounded decimal values.
Before troubleshooting specific cells, check the formula bar to ensure your calculation options are set to 'Automatic' rather than 'Manual' to rule out delayed calculation issues.
Convert Numbers Stored as Text to Actual Numbers
Mathematical formulas like SUM will ignore numbers formatted as text strings. Converting them back to numeric values resolves most addition issues.
When data is imported from external sources or formatted incorrectly, numbers may be stored as text. This prevents Excel from recognizing them as values, causing them to be excluded from addition formulas.
Select the cells containing the numbers you want to add. Look for a small green triangle in the top-left corner of the cells, which indicates a number stored as text.
Click the yellow warning icon with an exclamation mark that appears next to the selected cells, and choose 'Convert to Number' from the dropdown menu.
If the warning icon does not appear, create a helper column next to your data and use the formula =VALUE(A1) (replace A1 with your target cell) to force the conversion, then sum the new column.

Adjust Cell Formatting to Reveal Hidden Decimals
If your displayed numbers are rounded up or down, the formula will still sum the exact underlying values, making the resulting total appear mathematically incorrect.
Easily Troubleshoot and Calculate Formulas in WPS Spreadsheet
WPS Spreadsheet provides intuitive error-checking tools to quickly identify and convert numbers stored as text, ensuring your formulas add up correctly every time.
- 1. Open your file in WPS Office: Launch WPS Spreadsheet and open the document containing the calculation errors.
- 2. Locate the formula error: Highlight the cells that are not adding correctly. WPS will automatically flag numbers stored as text.
- 3. Apply the fix immediately: Click the error indicator icon to convert the values to numbers, or use the Home tab to adjust visible decimal accuracy.

Frequently Asked Questions
Why is my SUM formula returning zero?
This typically occurs if all the referenced cells in your SUM range are formatted as text. Spreadsheet programs ignore text values mathematically, resulting in a total sum of zero. Converting them to numbers will fix this.
Does calculation mode affect formula results?
Yes. If your calculation mode is accidentally set to 'Manual', formulas will not update automatically when you change cell values. You can switch this back to 'Automatic' in the Formulas tab under Calculation Options.
How can I quickly check if a cell contains text instead of a number?
By default, numbers align to the right side of a cell, while text aligns to the left. You can also use the =ISTEXT() or =ISNUMBER() functions on a cell to verify its data type.




