Sometimes when you add fields to the Values area of a pivot table, they appear as Count of Field instead of Sum of Field. In the screen shot below, the Quantity field shows the COUNT, and the TotalPrice field shows the SUM.
COUNT Summary Function
The COUNT summary function is the default if a field in the pivot table’s source data contains blank cells, or cells with text. For example, in a column of invoice dates, someone might have mistyped a month name, so it isn’t recognized as a valid date. Or, in a column of sales quantities, you might have typed “N/A”, instead of a number, or maybe you left a blank cell to fill in later.
In these cases, where the source column contains blank cells or text, the summary function defaults to Count; otherwise, it defaults to Sum.
Change the Summary Function
You can manually change the summary function from Count to Sum, after the Values fields have been added. Or, to make things easier, you can run a macro to change the summary function.
This sample macro changes all the Values fields in the first pivot table on the active sheet to use the Sum function (xlSum). The code refers to the pivot table by index number (1), instead of using a specific name (“PivotTable1″), and that makes the macro more flexible.
You can copy this code to a regular code module in your workbook, and run it when you want to change the summary functions for all the Value fields.
Sub SumAllValueFields() Dim pt As PivotTable Dim pf As PivotField Dim ws As Worksheet Set ws = ActiveSheet Set pt = ws.PivotTables(1) Application.ScreenUpdating = False pt.ManualUpdate = True For Each pf In pt.DataFields pf.Function = xlSum Next pf pt.ManualUpdate = False Application.ScreenUpdating = True Set pf = Nothing Set pt = Nothing Set ws = Nothing End Sub
Download the Sample File
You can download the Excel 2007 file with the Change Summary Function to Sum sample code.
The SumAllValueFields macro is stored on the modPivot code module in the sample file. You can run the macro in the sample file, or copy the Excel VBA code to a regular code module in another workbook, and run it there.
Pivot Power Add-in
If you’ve bought a copy of my Pivot Power Premium add-in, this feature is included. Just click the commands in the Data Field Functions group, and you can quickly change all fields to SUM, or any other summary function. Or, select specific value fields, and just change those to a different summary function.