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.

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.

Format Excel Charts Like a Pro!!!

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Get These Hacks Now For Free!

Keep Learning

Explore more →

Best Online Excel & VBA Courses (2022)

Choices, Choices, & MORE Choices! With so many educational options available on the internet these days, it can be overwhelming...

Create A Bar Chart With Separate Positive And Negative Colors

Our goal as analysts is to tell a story with our data and it is a well-known fact that data...

VLOOKUP Practice Example File [With Solutions]

What Is Vlookup? Vlookup stands for Vertical Lookup and is part of a group of functions that I like to...

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.