Pivot Table Issues

Fix Excel PivotTable Counting Numeric Values Instead of Summing

Steven ParkerSteven Parker Nov 22, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the source values

Open the PivotTable's source sheet and select the affected column of values, excluding the header.

2
Convert the values

Go to Data > Text to Columns and click Finish. This converts eligible text numbers to numeric values.

3
Refresh the PivotTable

Return to the PivotTable, right-click a cell inside it, and select Refresh.

4
Choose Sum

Right-click a value in the affected field and select Summarize Values By > Sum. Alternatively, open Value Field Settings and select Sum.

5
Check remaining entries

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.

Check the result: Text to Columns converts eligible values, but entries containing other text or errors may need individual correction. Blanks do not always prevent a field from being summed.
Work with PivotTables in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 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. 3. Refresh the PivotTable: Right-click inside the PivotTable and select Refresh.
  4. 4. Set the calculation to Sum: Open the affected value field's settings in the PivotTable and select Sum as the summary calculation.
Convert text numbers before summarizing PivotTable valuesOpen and edit Microsoft Excel XLS and XLSX filesWork with spreadsheets through a familiar interfaceUse WPS Office for free
microsoft office alternative - wps office

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.