Pivot Table Issues

Fix Excel PivotTable SUM Errors for Numbers Stored as Text

Steve KSteve K Nov 20, 2026 868 views

Question details

The user needs a PivotTable to sum a field whose values appear numeric but are treated as text.

Fix Excel PivotTable SUM Errors for Numbers Stored 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.
Before you start

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.

Solution 1Recommended

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.

1
Test the source values

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.

2
Convert numeric text

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.

3
Refresh the PivotTable

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.

Check and convert numeric text in the source data
COUNT does not confirm a numeric data type: A PivotTable can count nonempty text and numeric values, while SUM requires numeric values.
Free Microsoft Office alternative

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.

Inspect numeric source data and summarize it with PivotTablesOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

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.