summary functions The Pivot table is an incredibly powerful tool for summarising data. Excel will either default to summing or counting the field data but you can choose from 11 different functions that include min, max and StdDev as well as the more common Sum, count and Average.

Why does Microsoft Excel Sum Data instead of Counting data?

If the data you are pivoting contains only numeric data then Excel will sum the data. If there are any blank cells or text cells (other than the header), then Excel will count the data.

I quite often end up with blank cells in my pivot table data. For convenience, I will regularly base my pivot table on a data range that is much larger than the populated data range. I do this so that additional rows of data can be added without having to update the range that the pivot table is looking at. (It is much better to create dynamic pivot table ranges though.)

In the first example, my pivot table is pulling from the populated range and all my fields default to the sum function.

Changing multiple pivot table fields

In the second example, I have based my pivot table on the columns A:G. This includes many thousands of blank rows and as a result all the fields default to the Count function.

Changing multiple pivot table fields

How to change the Summary Calculation in a pivot table

To force Excel to use the Sum function instead of Count, right-click a pivot table cell in the column you wish to change. Choose Summarize Values By and then tick Sum.

Changing multiple pivot table fields

You can use the same method to select any of the other summary functions.

Can you change the summary calculation of multiple pivot table fields at once?

Unfortunately there isn’t an easy way to change multiple fields to the same function and right-clicking on a load of data fields gets boring very quickly. If you find yourself having to do this on a regular basis it is worth setting up a macro in your personal workbook so that you can call upon it to very easily change the summary function of every data field in your pivot table.

Here is a short macro that converts all the fields in a selected pivot table to the Sum function. Make sure you have selected the pivot table to change before playing the macro.

Public Sub PivotFieldsToSum()
' Cycles through all pivot data fields and sets to sum
' Created by Dr Moxie

Dim pf As PivotField
With Selection.PivotTable
.ManualUpdate = True
For Each pf In .DataFields
With pf
.Function = xlSum
.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With
End Sub

As requested by a commenter, here’s the macro again with the option for the user to select the type of summary function. It isn’t the neatest option as I struggled to pass the user input directly such as .Function = SubTotalType

Public Sub PivotFieldsToSumUserInput()
' Cycles through all pivot data fields and sets to sum
' Created by Dr Moxie
Dim SubTotalType As String
Dim pf As PivotField

'Add a user input box with default value xlSum
SubTotalType = InputBox("What type of summary do you want? Options are: xlSum, xlAverage, xlCount, xlMax, xlMin", "Summary Type", "xlSum")

With Selection.PivotTable
.ManualUpdate = True
For Each pf In .DataFields
With pf
'.Function = xlSum

'Cycle through predefined summary types
If SubTotalType = "xlMin" Then
.Function = xlMin
    ElseIf SubTotalType = "xlCount" Then
    .Function = xlCount
    ElseIf SubTotalType = "xlAverage" Then
    .Function = xlAverage
    ElseIf SubTotalType = "xlMax" Then
    .Function = xlMax
    Else
    .Function = xlSum
End If

.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With
End Sub