We’ve all been there. You create a pivot table, add your Values fields, and Excel thinks you want to Count them instead of Sum them just because you have a few blanks.
To fix it, you can click the yellow Count of Labor (for example), choose Value Field Settings, and change the aggregate. Or you can right click on any field and choose Summarize Values By and switch it to Sum. Both good options, but not good enough. I assigned Ctrl+Shft+A to this happy little customer and I’m toggling aggregates like crazy.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub SwitchAggregate() Dim pf As PivotField 'Make sure the activecell is in a pivot field On Error Resume Next Set pf = ActiveCell.PivotField On Error GoTo 0 If Not pf Is Nothing Then 'Toggle between sum and count If pf.Function = xlSum Then pf.Function = xlCount Else pf.Function = xlSum End If End If End Sub |
There’s probably a bug or two, but so far so good.
I’ve been using this code for years to change all aggregation to sum, and remove the “Sum of ” prefix. For the relatively few times I need to use count, I’ve been doing it manually, but I like your toggle solution.
Sub SumAllDataFields()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
On Error GoTo 0
If Not pt Is Nothing Then
pt.ManualUpdate = True
For Each pf In pt.DataFields
pf.Function = xlSum
pf.Caption = Space(1) & pf.SourceName
Next pf
pt.ManualUpdate = False
End If
End Sub
This is great. Adding it to my Personal Macro workbook…Thanks!
Nine times out of ten, it’s the Count I need (I count a lot of things), and what I’m counting is items. But they decided to make the item number an actual number, so Excel thinks I want to add up the numbers. So it’s into the Summarize Values By again.
@Dick: I’m coincidentally working on an update to my InstantPivot routine at http://dailydoseofexcel.com/archives/2014/02/12/instant-pivot-just-add-water/ that will offer to replace blanks with zeros (for numerical columns) or ” ” (for text columns) in a PivotTable’s source data, which would mean you would never get this COUNT behavior.
@Scott: Sometimes I have the same field in the Values area multiple times, which would cause your code to error out. I’m currently amending my InstantPivot routine to handle that eventuality too.
That routine creates a PivotTable and also automatically turn the source data range into an Excel Table. If you happen to run it on an existing PivotTable, it will retrospectively turn the source data into a Table if needed, and then re-point the Pivot at that Table, plus do all the kinds of formatting outlined above and a good deal more besides.
So I use it for new pivots and old pivots alike. One macro, one shortcut to learn, hours of giggling.
@Dick: Neat idea, I really like it!
Though in many cases, the “Count” is a good indication that the data is not as clean as I would expect it – e.g. somebody applying a pivot the whole column(s) – or having some blanks somewhere… So usually better to fix the problem at the origin than the change the symptoms… :-)
Would it be possible to augment choices to include Median or ,say, 80th Percentile, etc, etc?
@Peter: What, develop a solution to correct the actual cause of the problem? What madness is this! It is so much more fun to apply a symtomatic fix now and deal with the unintended consequences later!
You guys must work in one person offices.
Jeremy: You can cycle through more than just Count and Sum, but you’re limited to the aggregate options. Average, Count, CountNums, Max, Min, Product, StDev/P, Sum, Var/P.