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 Sub

Only Looking At The First Row

For Each cell In Selection.Rows(1).Cells
  'Insert Code Here.....
Next cell

To 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.Function

I 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.

 
xlConsolidationFunctionConstant
xlAverage-4106
xlCount-4112
xlcountNums-4113
xlMax-4136
xlMin-4139
xlProduct-4149
xlStDev-4155
xlStDevP-4156
xlSum-4157
xlUnknown1000
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!

As always, in order to download this article's example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

 
                   Already Subscribed? Click HERE to log-in to the "Example Files" section

                   Already Subscribed? Click HERE to log-in to the "Example Files" section

 

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)