Fix Excel PivotTable Counting Numeric Values Instead of Summing
Question details
The user wants an Excel PivotTable to sum numeric values instead of counting them.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A PivotTable uses a source column that contains numbers stored as text.
- Observed behavior
- Excel counts the values or reports that the field cannot be summarized with Sum. Changing the number format does not convert the text values to numbers.
Locate the source data column used by the PivotTable and check that it contains values intended to be numeric. Changing the cell format to Number is not enough to convert numbers stored as text.
Convert the source column to numbers and refresh the PivotTable
Use this method when the source values look like numbers but are stored as text.
Open the PivotTable's source sheet and select the affected column of values, excluding the header.
Go to Data > Text to Columns and click Finish. This converts eligible text numbers to numeric values.
Return to the PivotTable, right-click a cell inside it, and select Refresh.
Right-click a value in the affected field and select Summarize Values By > Sum. Alternatively, open Value Field Settings and select Sum.
If Sum is still unavailable or the result is incorrect, inspect the source column for text, errors, hyphens, and blanks. Correct entries that should be numeric, then refresh the PivotTable again.
Convert and sum PivotTable values in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, convert eligible text numbers in the source data, and refresh the PivotTable before selecting Sum.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Convert the source values: Select the source column values, then choose Data > Text to Columns and click Finish to convert eligible text numbers.
- 3. Refresh the PivotTable: Right-click inside the PivotTable and select Refresh.
- 4. Set the calculation to Sum: Open the affected value field's settings in the PivotTable and select Sum as the summary calculation.

Frequently Asked Questions
Why does my Excel PivotTable show Count instead of Sum?
Excel may treat the source values as text rather than numbers. Check the source column, convert eligible values using Data > Text to Columns > Finish, and refresh the PivotTable.
Will changing the cells to Number format convert text numbers?
No. Changing the format changes how values appear but does not convert text to numeric values. Use Data > Text to Columns or another conversion method first.
How do I change an existing PivotTable field from Count to Sum?
Right-click a value in the field and select Summarize Values By > Sum. You can also select Sum in Value Field Settings after converting the source values and refreshing the PivotTable.
What should I check if the PivotTable still will not sum correctly?
Inspect the source column for text, errors, hyphens, or missing values where numbers are expected. Correct the relevant entries and right-click the PivotTable to select Refresh.




