Switching Aggregates in Pivot Fields
Feb06
We’ve all been there. You create a pivot table, add your Values fields, and Excel thinks you want to Count them instead of Sum them just because you have a few blanks.
To fix it, you can click the yellow Count of Labor (for example), choose Value Field Settings, and change the aggregate. Or you can right click on any field and choose Summarize Values By and switch it to Sum. Both good options, but not good enough. I assigned Ctrl+Shft+A to this happy little customer and I’m toggling aggregates like crazy.
Sub SwitchAggregate()
Dim pf As PivotField
‘Make sure the activecell is in a pivot field
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo 0
If Not pf Is Nothing Then
‘Toggle between sum and count
If pf.Function = xlSum Then
pf.Function = xlCount
Else
pf.Function = xlSum
End If
End If
End Sub
Dim pf As PivotField
‘Make sure the activecell is in a pivot field
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo 0
If Not pf Is Nothing Then
‘Toggle between sum and count
If pf.Function = xlSum Then
pf.Function = xlCount
Else
pf.Function = xlSum
End If
End If
End Sub
There’s probably a bug or two, but so far so good.