Usually, when you add a numeric field to the Values area in a pivot table, it automatically uses the Sum function. Sometimes though, a field automatically uses the Count function.
Why does Excel use different functions are used for two fields that contain similar data?
- If there are blank cells, or non-numeric data in the field, the Count function is used as a default.
- For fields that contain all numeric data, the Sum function is the default.
Change the Function
Unfortunately you can’t set a default summary function in a pivot table. However, after the field has been added to the Values area, you can change its summary function:
- Right-click a cell in the field that you want to change, and click Summarize Values By.
- In the Summarize by list, select one of the functions.
Use Programming to Change Data to SUM
If you have too many fields to change manually, you can use a macro to change the summary function automatically. There is sample code here.
Use a Pivot Table Add-In
For an even easier solution, you can use my free pivot table add-in, that already has the summary function code in it. Just download and install the add-in, and run it when you want to change several fields, all at once.
__________