How to Fix an Excel PivotTable That Shows Count Instead of Sum
This guide explains why an Excel PivotTable shows Count instead of Sum and the fastest ways to fix it. Change the Value Field Settings, force values to numbers, and lock data types with Power Query to prevent the issue from recurring.
Quick Fix (3 Minutes)
- Right-click the field in the Values area → Value Field Settings → select Sum.
- Force the source data to numbers: finish Text to Columns or use Paste Special = Multiply by 1.
- Right-click the PivotTable → Refresh.
Why It Shows Count Instead of Sum
If even one text value, blank, or error is mixed into a number column, the PivotTable may default to Count. Clean up the column as numeric data.
Solutions by Situation
Change Value Field Settings
In Value Field Settings, change the calculation to Sum, then use Number Format to apply a format such as #,##0.
Fix Numbers Mixed with Text or Blanks
=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))For European-style number formats, use =NUMBERVALUE(A2,",",".").
Handle Hyphens and N/A Values
Replace hyphens with 0, and use IFERROR to convert N/A values to 0 or exclude them.
Use Multiple Summary Calculations
Add the same field to the Values area twice to display the sum, average, and percentage at the same time.
Prevent Recurrence with Power Query or the Data Model
- When importing data, turn off automatic type detection and set number columns to Whole Number or Decimal Number.
- Replace hyphens and other characters with 0 or null.
- Add the data to the Data Model to lock the data types.
Practical Examples
// Remove NBSP, then convert to a number
=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))
// European format → Korean format
=NUMBERVALUE(A2, ",", ".")
Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| Only shows Count | Text or blanks are mixed in | Text to Columns / Multiply by 1 / VALUE |
| Reverts after changing to Sum | Source data type is text | Force the type with Power Query |
| Sum is 0 or incorrect | NBSP, European number format, or hyphens | Remove CHAR(160), use NUMBERVALUE, or replace values |
| Formatting is lost | PivotTable-specific formatting not applied | Value Field Settings > Number Format |
Related Articles
- PivotTable 10-Minute Quick Start Guide
- Fix Numbers Stored as Text
- Preprocess Data with TEXTSPLIT
- Practical SUMIFS and AVERAGEIFS Patterns
- Create an Excel Drop-Down List