If you put a field in the values area of a PivotTable and it defaults to COUNT as opposed to SUM, the reason is that the column has at least one cell containing text, or one cell that is empty. Yes, one pesky cell that doesn't contain a number is enough for a PivotTable to return COUNT.
It’s annoying but unfortunately the only way to make a PivotTable default to SUM instead of COUNT is to make sure your column of data contains a number in every cell. You see the PivotTable is trying to determine the type of data you have and apply a relevant aggregation function for you. It’s trying to be helpful.
Solution: Right-click a value in the column you want to change > Summarize Values By > Sum:
Highlight Text, Numbers, Dates and Formulas on a Sheet
Junaid Tahir
Can i use VBA code to change the field setting in pivot table with variable value field name.
Mynda Treacy
I expet so, yes, but I haven’t tried it before.
Brendan Kelly
Hi,
I “Control H” my data where possible…find and replace.
Find “Blanks” and Replace them with zeroes…that way there are no blanks.
I hope this helps someone,
Brendan.
Mynda Treacy
Thanks for sharing, Brendan.