When you add a numeric field to the Values area of a pivot table, it usually appears as a Sum. For example, in the pivot table shown below, the Units field became Sum of Units.
Occasionally, when you add a numeric field to the Values area, it shows the Count, instead of the Sum. In the same pivot table, when the TotalSales field is added, it shows a Count.
Pivot Table Default Function
If you add a number field to the Values area, the default summary function is Sum. However, if the source data has blank cells, or nonnumeric data, such as text or errors, in that field, the Count function is the default summary function.
You can’t change the default settings for the data fields, but you can manually change the summary function after you add the fields to the Values area.
To manually change the summary function:
- Right-click a cell in the field you want to change, and click Summarize Data By.
- Click the summary function that you want to use.
Pivot Table Add-in
There’s a feature that changes all the data fields to SUM, or any other summary function, in my pivot table add-in. It add a new tab to the Ribbon, with time-saving commands that you can use.
- After you install the add-in, select any cell in the pivot table.
- Then, on the Ribbon’s Pivot Power tab, click SUM ALL.
- Or, click Change ALL to, then click the Summary function that you want to use.
For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.