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:
Can i use VBA code to change the field setting in pivot table with variable value field name.
I expet so, yes, but I haven’t tried it before.
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.
Thanks for sharing, Brendan.