Fix Excel PivotTable SUM Errors for Numbers Stored as Text
Question details
The user needs a PivotTable to sum a field whose values appear numeric but are treated as text.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- A user wants to summarize a numeric-looking source field with SUM rather than COUNT.
- Observed behavior
- The PivotTable allows COUNT but reports that the field cannot be summarized with SUM. The user reports that ISNUMBER returned TRUE for every tested value.
Locate the PivotTable's source range using PivotTable Analyze > Change Data Source, and identify the column used in the Values area. Check the entire source column, including any newly added rows, for blanks, errors, or text.
Check and convert numeric text in the source data
Use this method when one or more source values look like numbers but are stored as text.
In an empty worksheet column, enter =ISNUMBER(A2), replacing A2 with the first source cell, and fill the formula down. TRUE indicates a numeric value; inspect FALSE results for text, blanks, or errors.
Select the affected source cells and choose Data > Text to Columns > Finish. Alternatively, use the warning icon's Convert to Number command when it appears. Check that the values are correct after conversion.
Right-click the PivotTable and select Refresh. In the Values area, open the field's Value Field Settings and select Sum under Summarize Values By.

Investigate the PivotTable when all tested values are numeric
Use these checks if ISNUMBER returns TRUE but the PivotTable still cannot use SUM.
Analyze PivotTable Source Data with WPS Spreadsheets
WPS Office is a free, lightweight alternative for working with spreadsheet data. Its familiar interface and Microsoft Excel format support make it easy to open existing workbooks and continue your analysis.

Frequently Asked Questions
Why does my PivotTable use COUNT instead of SUM?
Excel may treat the source field as nonnumeric, for example if it contains numeric text. Test the source cells with =ISNUMBER(A2), then convert numeric text and refresh the PivotTable.
What if ISNUMBER returns TRUE for every value?
Check the full source range via PivotTable Analyze > Change Data Source, then refresh the PivotTable. If SUM still fails, try creating a new PivotTable or check the data model or external source settings.
How do I change a PivotTable field from COUNT to SUM?
Open the field's Value Field Settings from the Values area, then select Sum under Summarize Values By. If Sum is unavailable or fails, inspect the source data type and refresh the PivotTable.
Can I share a workbook for further investigation?
Yes. Create a sample workbook without confidential or sensitive data, upload it to OneDrive, and share the link with the person investigating the issue.




