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
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
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
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
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.
Maybe blanks is what causes it for me. I would swear that it counted all numbers, but now I’ll watch it more carefully.
Thanks to everyone for the help. This site is great.
Rick’s function is clever. If the current function is xlCount, then the function will change to xlSum, and vice versa.
@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.
Your else clause is a bit redundant, isn’t it? ;-)
Brilliant – this will save so much time. Thanks, you rock!
@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
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.
@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.
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.
@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…
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
. 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….
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.
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”
@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).
Hi,
Great tip…
Have added a test to check whether the pivot is connected to an OLAP source
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
What if I want to toggle between Sum, Count and Average instead of just two functions?
You can put however many you want in vaFunctions