Pivot Tables Count vs Sum

[Siderias38]

When I'm creating a Pivot table some of my numbers default to count instead of Sum. Is there any way I can fix this in a setting so going forward I don't have to keep changing it or do I need to change the value settings every time?

Thank you

 

[Jerry Sullivan]

Welcome to MrExcel,

The aggregate type will default to Count if Excel interprets that not all the values in that field are numbers. If you have any blanks in the field the default will be Count. So you can avoid that if you use zeros instead of blanks and have all numbers.

There isn't a setting in Excel that will automatically use Sum for a new field. As an alternative, you could use a VBA macro to add new fields (using Sum).