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:
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.