When a PivotTable Shows “Count” Instead of “Sum” — How to Change It to “Sum” by Converting Values to Numbers
To change a PivotTable count to a sum, set the value field setting to “Sum” and clean up text numbers, spaces, and errors so they are recognized as numbers. Prevent the issue from recurring with a Table (Ctrl+T) or Power Query.
Quick Fix (90 Seconds)
- Right-click a PivotTable value cell → Value Field Settings → Sum.
- Re-evaluate the source column with Text to Columns (Finish).
- Convert the source range to a Table (Ctrl+T), then Refresh.
Why Does It Show “Count”?
If the values are not numeric, the PivotTable suggests Count as the default summary calculation. Text numbers, spaces, and errors can cause values to be recognized as text.
Convert All Values to Numbers
// Simplest method
Data > Text to Columns > Finish
// Remove spaces and nonbreaking spaces
=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),"")))
// Remove commas
=VALUE(SUBSTITUTE(A2,",",""))
// Convert errors to 0 (optional)
=IFERROR(VALUE(A2),0)Value Field Settings & Number Format
In Value Field Settings, select Sum, then set the number format under Number Format at the bottom.
Automatic Expansion
- Convert the source to a Table (Ctrl+T), then set the PivotTable source to the table name.
- Use the Data Model and Refresh All.
Lock in Numeric Values with Power Query
Set the data type to Number during import and specify the locale.
Practical Example
// Convert amounts to numbers
=IFERROR(VALUE(SUBSTITUTE(D2,",","")),0)Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| Only Count appears | Text values/errors | Text to Columns, VALUE, IFERROR |
| Incorrect sum | Commas/spaces | SUBSTITUTE, TRIM |
| New data is not included | Fixed range | Table, Refresh |
Related Posts
- TEXTSPLIT preprocessing
- Automatically expand data
- Fix CSV encoding issues
- Print an Excel worksheet on one page
- SUMIFS and AVERAGEIFS patterns