Background
I was preparing a report for the Veterans Health Administration and I ran into an inconvenience where I had to convert the values in a pivot table from the default COUNT to SUM. Normally, this would not be an onerous process. However, there were several columns that I wanted to convert, which would take an enormous amount of work to perform. Fortunately, I ran into Doctor Moxie's blog, which providers tips and tricks to using Excel efficiently. You can find the blog here.
Doctor Moxie created a Visual Basic Macro that conveniently converts all the data in the pivot table from the default COUNT to SUM.
Motivating Example
To illustrate the solution, I used the following example dataset, which was generated using the following function:
=RANDBETWEEN(0, 100)
This will generate a value between 1 and 100 for each cell.
I used the pivot table to see see the number of hours per region. From the pivot table, the values were reported as COUNT per region instead of SUM per region.
I could go through each column and change the values from COUNT to SUM, but this would be inconvenient if there were over 100 columns.
Doctor Moxie developed an inventive solution to address this problem using a macro, which you can get from his/her website.
Using the macro converted the values from COUNT to SUM.
The macro nicely converts all the cells and makes the analysis easier to perform. This saved me hours of work and I recommend you keep this macro in your Excel arsenal.
Acknowledgements
Doctor Moxie is credited with developing the macro and his/her website (ExcelPivots) can be found here.