Pivot Table summarising as COUNT, not SUM.

A really quick tip today. Some of you may find when inserting a pivot table, that your numeric values are counted rather than summed:

Excel automatically counts the items in the data field, rather than summing their values.
Excel automatically counts the items in the data field, rather than summing their values.

Why? Well usually, this is down to missing data – blank values in your numeric columns. Since (blank) can’t be summed, Excel reverts to counting the values that are available.You can see the source problem in my bogus data here:

Blank Data Points cause Counts rather than Sums
This is caused by items in the dataset where the numeric field is blank

This is relatively easy to sort – we just need to change the “Summarise Values By” option to Sum, and we’re off – Excel wil do the rest for you. However, when there are a lot of columns to run through, this can be time consuming, and not just a little bit boring:

One handy tip is to do this with a combination of the keyboard and mouse. Using the “Summarise Values By” option in the Pivot Table Tools Options context menu in the ribbon is much quicker, and works on the active field in your table. Utilising this function, and the arrow keys to switch between fields makes the process much quicker.

Just a short hint, but I hope you find it useful!

No ratings yet.

Please rate this