Fix Excel SUM Returning Zero for Dollar Amounts Stored as Text
Question details
The user needs to convert dollar amounts stored as text into numeric values so the SUM function returns the correct total.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Dollar values such as $10,000 and $20,000 are entered or imported into worksheet cells and need to be added together.
- Observed behavior
- SUM returns $0.00 because the cells contain text strings rather than numeric currency values.
Select one of the affected cells and check the formula bar for leading apostrophes, spaces, or imported currency characters. Use =ISNUMBER(A1) or =ISTEXT(A1) in an empty cell to confirm whether the amount is numeric or text.
Convert text dollar amounts with Text to Columns
Use this quick method when the cells contain consistently formatted dollar amounts and you want to convert an entire selected range at once.
Text to Columns can force Excel to reinterpret text entries as numbers. After conversion, apply a Currency or Accounting format to restore the dollar display.
Highlight the cells or column containing the dollar amounts that SUM is ignoring.
On the Excel ribbon, select Data > Text to Columns.
Choose Delimited if prompted, then select Next until the final screen and click Finish without selecting a delimiter. Excel converts recognizable text amounts to numbers.
With the converted cells selected, open Home > Number Format and choose Currency or Accounting. Then use =SUM(A1:A10) for the converted range.
Use a formula to remove dollar signs and commas
Use this method when Text to Columns does not convert the imported values or when you need to preserve the original text column.
Convert Text Currency Values and Calculate Totals in WPS Spreadsheet
WPS Spreadsheet provides the same practical tools for converting imported dollar amounts from text to numbers. Use Text to Columns or a VALUE formula, apply a currency format, and then total the corrected values with SUM.
- 1. Open the worksheet in WPS Spreadsheet: Open the Excel workbook in WPS Office and select the cells containing text-formatted dollar amounts.
- 2. Use Text to Columns: Go to Data > Text to Columns, continue through the wizard, and select Finish to convert the selected text values.
- 3. Apply currency formatting: Select the converted cells and choose Home > Number Format > Currency or Accounting.
- 4. Calculate the total: Click an empty result cell, enter =SUM(range), and press Enter to total the converted numeric amounts.
Frequently Asked Questions
Why does Excel SUM ignore my dollar amounts?
SUM ignores cells that contain text. Dollar amounts may be text because they were imported with currency symbols, commas, spaces, or a leading apostrophe instead of being stored as numeric values.
How can I tell whether a currency cell is text or a number?
Enter =ISNUMBER(A1) and =ISTEXT(A1) in empty cells, replacing A1 with the amount cell. ISNUMBER returns TRUE for a numeric value, while ISTEXT returns TRUE for text.
Will changing the cell format from Text to Currency fix the SUM result?
No. Changing Home > Number Format only changes the display format; it does not convert an existing text string to a number. Use Data > Text to Columns or a VALUE conversion formula first.
What formula converts $10,000 stored as text into a number?
Use =VALUE(SUBSTITUTE(SUBSTITUTE(A1,"$",""),",","")), where A1 contains the text amount. The result is numeric and can be included in SUM.
Why does Text to Columns not convert one of my dollar values?
The cell may contain hidden spaces or nonstandard characters from an import. Clean the value with TRIM and use =VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(A1),"$",""),",","")) to convert it.
