Fix Excel SUM or SUMIFS Returning Zero for Text-Formatted Numbers
Question details
The user needs to troubleshoot an Excel SUM or SUMIFS formula that returns zero although the matching criteria appear to be correct.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Adding values with a SUM or SUMIFS formula based on matching table criteria.
- Observed behavior
- The formula returns 0 because the values in the sum range may be stored as text instead of numeric values.
Select a few cells in the range being summed and check their alignment and number format. Text-formatted numbers often display a green warning triangle or remain left-aligned by default.
Convert text-formatted numbers in the SUM or SUMIFS range
Use this solution when the formula criteria are correct but the cells being added are stored as text.
SUM and SUMIFS ignore numbers stored as text. Convert the values in the sum range to real numbers, then recalculate the formula.
Select the cells or table column used as the first argument of your SUMIFS formula, such as Table1[Initiative Size].
If a green triangle appears in a selected cell, click the warning icon and choose Convert to Number.
For multiple cells, select the range, go to Data > Text to Columns, keep Delimited selected, click Finish, and let Excel convert the text values to numbers.
Click the formula cell and press Enter. For the table example, use =SUMIFS(Table1[Initiative Size],Table1[Which Superintendency Will Develop the Initiative],[@Superintendency],Table1[Initiative Status],"Prioritized").
Convert text numbers by multiplying the range by 1
Use this quick method when you need to convert many text numbers without changing the displayed values.
Convert Text Numbers and Repair SUMIFS Results in WPS Spreadsheets
WPS Spreadsheets lets you identify text-formatted numbers, convert them to numeric values, and use SUM or SUMIFS formulas to calculate matching records accurately.
- 1. Open the worksheet in WPS Spreadsheets: Open the Excel workbook in WPS Office and select the worksheet containing the SUM or SUMIFS formula.
- 2. Select the sum range: Select the cells or table values referenced by the SUMIFS sum_range argument, such as the Initiative Size column.
- 3. Convert the values to numbers: Go to Data > Text to Columns and click Finish, or use Home > Paste Special > Multiply after copying a cell containing 1.
- 4. Enter or refresh the formula: Select the result cell and enter =SUMIFS(Table1[Initiative Size],Table1[Which Superintendency Will Develop the Initiative],[@Superintendency],Table1[Initiative Status],"Prioritized"), then press Enter.
Frequently Asked Questions
Why does SUMIFS return 0 when my criteria match?
A common cause is that the sum range contains numbers stored as text. SUMIFS can match the criteria but ignores text values in the range it is supposed to add.
How can I tell whether a number is stored as text in Excel?
Click the cell and look for a green warning triangle or warning icon. You can also check whether the value is left-aligned by default or change the cell format to Number and test whether it converts.
Will Text to Columns fix numbers stored as text?
Yes. Select the affected range, choose Data > Text to Columns, leave the default conversion options in place, and click Finish to convert many text numbers at once.
What is the correct SUMIFS formula for the Initiative Size table?
Use =SUMIFS(Table1[Initiative Size],Table1[Which Superintendency Will Develop the Initiative],[@Superintendency],Table1[Initiative Status],"Prioritized"). Ensure that Table1[Initiative Size] contains numeric values.
Can I use VALUE to convert a text number in a formula?
Yes. Use =VALUE(A1) to convert a text number in A1 to a number. For a full data range, converting the source cells with Convert to Number, Text to Columns, or Paste Special > Multiply is usually more practical.
