Formula guide · Excel and Google Sheets · Updated May 13, 2026
Pivot Table Shows Count Instead of Sum
Fix a pivot table value field that counts records instead of summing numbers.
Quick Answer
A pivot table usually shows Count when the source amount column contains text, blanks, or non-numeric values.
Copyable Formula
Clean the source numbers, then set Values to Sum.Syntax
Value Field Settings > SumExcel and Google Sheets
Worked Example
| Symptom | Likely cause |
|---|---|
| Count of Amount | Text or blanks in amount column |
| Wrong total | Mixed data types |
| Cannot sum | Numbers stored as text |
Result: After the source column is numeric, the pivot table can summarize it as Sum.
Steps
- Check whether the source amount column contains text values.
- Convert text numbers to real numbers.
- Refresh the pivot table.
- Change Value Field Settings from Count to Sum.
Common Mistakes
- Changing the pivot setting without fixing the source data.
- Ignoring blank rows inside the source table.
- Using formatted text such as $120 instead of numeric values.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.