Calculation Issues

Fix Excel SUM Returning Zero for Text Numbers on Mac

Anum MushtaqAnum Mushtaq Nov 18, 2026 869 views

Question details

The user needs to include numbers stored as text in Excel SUM calculations on a Mac.

Fix Excel SUM Returning Zero for Text Numbers on 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.
Before you start

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.

Solution 1Recommended

Convert text numbers with the VALUE function

Use a helper column to convert values before summing them without changing the original cells.

1
Add a conversion formula

In an empty cell beside the first value, such as B2, enter =VALUE(A2), replacing A2 with the first affected cell.

2
Fill the formula down

Select B2 and drag its fill handle down alongside the remaining values.

3
Sum the converted 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 text numbers with the VALUE function
If VALUE returns an error: Check the source cells for extra characters or separators that Excel does not recognize under your regional settings, then correct them and recalculate.
Work with spreadsheets in WPS Office

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 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. 3. Fill down: Select the formula cell and drag its fill handle down beside the remaining values.
  4. 4. Calculate the total: In a cell outside the helper range, enter =SUM(B2:B100), adjusting the range to match the converted cells.
Convert text numbers with the VALUE functionUse SUM to total the converted valuesOpen and save Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.