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

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!

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.

**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 :)