Toggle PivotFields from Count to Sum

Carrie laments:

Is there a way to change the default in a pivot table so sums the values when created rather than count? It is very time consuming if you have several data points.

I hear ya, sister. Excel does a pretty good job guessing, but sometimes it gets stuck on Count like that’s all I want to do. I don’t know of any way to force it to Sum or Count or any of the other functions available. So I wrote this little macro the make the job easier.

Select a cell in the PivotField and run this macro to toggle between counting and summing.

Sub ToggleCountSum()
   
    Dim pf As PivotField
   
    On Error Resume Next
        Set pf = ActiveCell.PivotField
    On Error GoTo 0
   
    If Not pf Is Nothing Then
        If pf.Function = xlCount Then
            pf.Function = xlSum
        Else
            pf.Function = xlCount
        End If
    End If
   
End Sub
Posted in Uncategorized

19 thoughts on “Toggle PivotFields from Count to Sum

  1. It probably worth understanding why it “sometimes” shows Count.

    a) If the data column has only numbers (excluding the header) then it always summaries by SUM
    b) If the data column has mixed datatypes (blanks, text, text numbers etc) then it defaults to COUNT

  2. I think you can do your macro with much fewer lines of code…

    Sub ToggleCountSum()
    On Error Resume Next
    ActiveCell.PivotField.Function = xlCount + xlSum – ActiveCell.PivotField.Function
    End Sub

  3. To continue on what Sam is saying: If you select your data set by choosing entire columns (Sheet1!$A:$C instead of Sheet1!$A$1:$C$3) by default you end up including blanks which causes Excel to default to CCOUNT.

  4. Rick’s function is clever. If the current function is xlCount, then the function will change to xlSum, and vice versa.

  5. @sam

    Thanks! That helps a lot.

    @Dick

    Thanks for the code. I’ve bookmarked it for the future. Like you I’m going to observe more closely when Excel wants to count.

  6. @Rob,

    No, Dick’s Else clause in not redundant… if pf.Function is not equal to xlCount, the Else clause makes it so. In psuedo-code…

    If pf.Function equals xlCount Then
    We are here because pf.Function equals xlCount, so make it equal to xlSum instead
    Else
    We are here because pf.Function equals xlSum, so make it equal to xlCount instead
    End If

  7. I love that function, Rick. For those scratching your heads, it basically says “Here’s both options, now take away the one that I already have, and just leave me the one I don’t.

  8. @jeff weir (and JP too),

    Yep, that’s all there is to the toggle routine… add two numbers together and then subtract one of the number and it automatically leaves the other. I’ve been posting variations of that function for some 9 or 10 years now, originally to the compiled VB newsgroups where I was an MVP before becoming one for Excel. Strange, though, even after posting it to the old newsgroups against messages that used the If..Then approach, when the question would come up again, the people who posted the If..Then method originally would end up using the If..Then method over again… for some reason I couldn’t convince people to use my method exclusively… I can’t figure out why. With that said, it should be noted that the concept only works when the variable and/or property being toggled is already set to one of the two toggle values. Otherwise, you would have to handle it something like this…

    ‘ Place this line so that it has proper scope for the routines that need access to it (note the Data type)
    Dim TheToggleValue As Variant

    ‘ Put this code in the macro that performs the toggle
    If IsEmpty(TheToggleValue) Then TheToggleValue = <>
    TheToggleValue = <> + <> – TheToggleValue

    Doing that will set the Toggle value to <> the first time through the code. AND, this concept can be used to toggle between two words or phrases as well (but care must be taken to make sure one of the toggle strings is not wholly contained inside the other). Consider this….

    ‘ Place this line so that it has proper scope for the routines that need access to it (note the Data type)
    Dim TheToggleWord As String

    ‘ Put this code in the macro that performs the toggle
    If Len(TheToggleValue) = 0 Then TheToggleValue = “WordTwo”
    TheToggleValue = Replace(“WordOne” & “WordTwo”, TheToggleValue, “”)

    Doing that will set the Toggle value to WorkOne the first time through the code.

  9. A Clarification…

    In my last posting, I described how my “toggle concept” could be applied to text strings as well as numbers. In that message I said this…

    >> but care must be taken to make sure one of the toggle
    >> strings is not wholly contained inside the other

    You also must watch out that one of the text strings is not contained across the concatenation point for the two text strings either. If that happens, you can handle it easily enough by concatenating one or more space characters (as many as are needed to avoid a match across the concatenation point) and then Trim the final result from the Replace function. There is probably not a lot you can do (efficiently) to handle the one word or phrase embedded within the other problem.

  10. @jeff weir (and JP too),

    In my message directed at the both of you, I just noticed that the comment processor “ate” the text in between the less than and greater than symbols. I’m going to repost that message (coupled with the clarification I posted afterwards), but use the code tags to see if that will post the code correctly…

    Yep, that’s all there is to the toggle routine… add two numbers together and then subtract one of the number and it automatically leaves the other. I’ve been posting variations of that function for some 9 or 10 years now, originally to the compiled VB newsgroups where I was an MVP before becoming one for Excel. Strange, though, even after posting it to the old newsgroups against messages that used the If..Then approach, when the question would come up again, the people who posted the If..Then method originally would end up using the If..Then method over again… for some reason I couldn’t convince people to use my method exclusively… I can’t figure out why. With that said, it should be noted that the concept only works when the variable and/or property being toggled is already set to one of the two toggle values. Otherwise, you would have to handle it something like this…

    ‘ Place this line so that it has proper scope for the routines that need access to it (note the Data type)
    Dim TheToggleValue As Variant

    ‘ Put this code in the macro that performs the toggle
    If IsEmpty(TheToggleValue) Then TheToggleValue = <Value2>
    TheToggleValue = <Value1> + <Value2> – TheToggleValue

    Doing that will set the Toggle value to

    <Value1>

    . the first time through the code. AND, this concept can be used to toggle between two words or phrases as well (but care must be taken to make sure one of the toggle strings is not wholly contained inside the other)**. Consider this….

    ‘ Place this line so that it has proper scope for the routines that need access to it (note the Data type)
    Dim TheToggleWord As String

    ‘ Put this code in the macro that performs the toggle
    If Len(TheToggleValue) = 0 Then TheToggleValue = “WordTwo”
    TheToggleValue = Replace(“WordOne” & “WordTwo”, TheToggleValue, “”)

    Doing that will set the Toggle value to WorkOne the first time through the code.

    **Clarification: You also must watch out that one of the text strings is not contained across the concatenation point for the two text strings either. If that happens, you can handle it easily enough by concatenating one or more space characters (as many as are needed to avoid a match across the concatenation point) and then Trim the final result from the Replace function. There is probably not a lot you can do (efficiently) to handle the one word or phrase embedded within the other problem.

  11. Thanks Rick. I’d be interested to know about what kind of uses you woud put the word toggle to…the immediate use that came to mind (apart from toggling between Fu and Bar during work hours) would be to update some kind of display that tells you the state of a property along the lines of “Self Destruct is now ON” vs “Self Destruct is now OFF”

  12. @Jeff,

    I didn’t have a specific use in mind, I just wanted to point out if you ever had a need to toggle between two words or phrases (maybe reporting an On/Off, Open/Closed, In/Out etc. type condition would be a possible use… think UserForm Label display rather than spreadsheet cell usage here) that it could be done in one line of code rather than the standard 5-line If..Then..Else block (in a similar conceptual way that one can toggle between two numerical values).

  13. Hi,

    Great tip…

    Have added a test to check whether the pivot is connected to an OLAP source

        Set pv = pf.Parent.PivotCache
       
        If Not pv.OLAP Then
       
            If Not pf Is Nothing Then
                If pf.Function = xlCount Then
                    pf.Function = xlSum
                Else
                    pf.Function = xlCount
                End If
            End If
       
        End If
  14. What if I want to toggle between Sum, Count and Average instead of just two functions?

  15. You can put however many you want in vaFunctions


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *