Fix Excel SUM Returning Zero for Text Numbers on Mac
Question details
The user needs to include numbers stored as text in Excel SUM calculations on a Mac.

- Product
- Microsoft Excel for Mac
- Device & OS
- macOS
- Scenario
- A worksheet contains columns of apparent numbers that need to be added with SUM.
- Observed behavior
- SUM returns zero for some columns, possibly because their values are stored as text.
Select a few affected cells and set their alignment to General; values that remain left-aligned may be stored as text. Check that the SUM formula references the intended cells and that their decimal and thousands separators match your regional settings.
Convert text numbers with the VALUE function
Use a helper column to convert values before summing them without changing the original cells.
In an empty cell beside the first value, such as B2, enter =VALUE(A2), replacing A2 with the first affected cell.
Select B2 and drag its fill handle down alongside the remaining values.
In a cell outside the helper range, enter a formula such as =SUM(B2:B100), adjusting the range to include your converted values.

Convert a column using Text to Columns
Use this method to convert text numbers in place when the selected cells contain one value each.
Convert and Sum Text Numbers in WPS Spreadsheets
Open your spreadsheet in WPS Spreadsheets, use VALUE in a helper column to convert text numbers, and sum the converted values.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Convert the first value: In an empty cell such as B2, enter =VALUE(A2), replacing A2 with the first cell containing a text number.
- 3. Fill down: Select the formula cell and drag its fill handle down beside the remaining values.
- 4. Calculate the total: In a cell outside the helper range, enter =SUM(B2:B100), adjusting the range to match the converted cells.

Frequently Asked Questions
Why does Excel SUM ignore numbers that I can see?
SUM ignores values stored as text in the referenced cells. Use =VALUE(A2) in an empty cell to test whether Excel can convert an affected value to a number.
Does left alignment always mean a number is stored as text?
No. When alignment is set to General, left alignment can indicate text, but custom alignment can override that clue. Test an affected cell with =ISNUMBER(A2); FALSE indicates the value is not numeric.
Can I convert text numbers without changing the original column?
Yes. Enter =VALUE(A2) in an empty helper column, fill it down, and use SUM on the helper cells. The original values remain unchanged.
What should I check if SUM still returns zero after conversion?
Check that the SUM formula references the converted cells rather than the original text cells. Also check for conversion errors and verify that Excel's calculation settings allow formulas to recalculate.




