Quickly Change Pivot Table Field Calculation From Count To Sum
A Quick Demo Using The Solution
Do You Have This Pivot Table Problem?
I absolutely hate wasting time! Any time I’m repeating something over and over again just to get the proper format, I want to gouge my eyes out! Pivot Table creation and formatting is very high on my list as it can be very manual. Have you every created a multi-column pivot table where you wanted to sum and the pivot table defaulted to counting? This happens to me all the time and unfortunately you can batch change a pivot field’s calculation. You have to go into each individual column and change the Summarize By calculation. Well, in order to save my eyes from a gouging (they are kind of important for a data analyst) I decided to create a personal macro to do all this repetitive mouse clicking for me. What I came up with was a simple and very fast way to easily toggle between summing and counting my pivot table data. I’ll go ahead and give you the code first and provide a little commentary below it for those who need some help understanding what is going on. Enjoy!
Sub PivotToggleCountSum()
'PURPOSE: Toggles between Counting and Summing Pivot Table data columns from current cell selection
'SOURCE: www.TheSpreadsheetGuru.com
Dim pf As PivotField
Dim AnyPFs As Boolean
Dim cell As Range
AnyPFs = False
'Optimize Code
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Cycle through first row of selected cells
For Each cell In Selection.Rows(1).Cells
On Error Resume Next
Set pf = cell.PivotField
On Error GoTo 0
If Not pf Is Nothing Then
'Toggle between Counting and Summing
pf.Function = xlCount + xlSum - pf.Function
'Format Numbers with Custom Rule
pf.NumberFormat = "#,##0_);(#,##0);-"
'No need for error message
AnyPFs = True
'Reset pf variable value
Set pf = Nothing
End If
Next cell
'Did user select cells inside a Pivot Field?
If AnyPFs = False Then MsgBox "There were no cells inside a Pivot Field selected."
'Optimize Code
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubOnly Looking At The First Row
For Each cell In Selection.Rows(1).Cells
'Insert Code Here.....
Next cellTo speed up this code, I used a little Pivot Table logic. Since summarizing a Pivot Field can only be applied to an entire column, I only need to look at a single cell in each Pivot Table Field. Instead of looping through every cell in the user selection range, I narrow the range I want to work with down to just the first row of the user’s selection.
A Little Constant Logic
On Error Resume Next
Set pf = cell.PivotField
On Error GoTo 0
'Toggle between Counting and Summing
pf.Function = xlCount + xlSum - pf.FunctionI could have used an If/Then statement to toggle between the Sum and Count functions but I wanted to show you an alternative (some may say more creative) way of toggling. Each one of the Pivot Field Functions has a constant (a fancy computer programming word for numerical value) associated with it and guess what….we can do math with those values! By looking in the Visual Basic Editor’s Object Library (shortcut F2) we can determine that xlSum’s constant value is -4157 and xlCount’s constant value is -4112. Let’s assume that the xlSum is currently what the Pivot Fields are being summarized by. If we look at our VBA code’s calculation, our values would be the following:
- xlCount = -4112
- xlSum = -4157
- pf.Function (current pivot field calculation)= -4157
Our calculation would be pf.Function = (4112) + (4157) – (4157) and pf.Function = -4112. So after the code would have run, the pivot field function would have been switched from xlSum to xlCount. Pretty cool, huh? Here are all the constant values for the xlConsolidationFunction collection in case you are wanting to toggle between other Pivot Field Functions.
| xlConsolidationFunction | Constant |
| xlAverage | -4106 |
| xlCount | -4112 |
| xlcountNums | -4113 |
| xlMax | -4136 |
| xlMin | -4139 |
| xlProduct | -4149 |
| xlStDev | -4155 |
| xlStDevP | -4156 |
| xlSum | -4157 |
| xlUnknown | 1000 |
| xlVar | -4164 |
| xlVarP | -4165 |
Add In A Splash Of Formatting
'Format Numbers with Custom Rule
pf.NumberFormat = "#,##0_);(#,##0);-"I threw in one of my “go-to” number formats as I also like to change my number formatting to something that includes comma separators. You can modify this line to any format you would like or take it out completely if you don’t wish to change the number format.
Now You’re Ready To Format Some Pivot Fields!
Now you have the VBA code and understand what it does. Add this snippet to your personal macros (how to create a personal macro file) and start saving time while creating all those vital Pivot Tables. Leave a comment below if you have any questions or think of any modifications that may make this code more versatile!
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![Excel Guru’s Top Interview Questions To Ask [What I Look For]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/12/Best-Excel-Skills-Interview-Questions.png)
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.