How to count unique records by group in an Excel PIVOT table

An interesting problem posed to me recently by a client was how to get a count of unique records by group in an Excel PIVOT table. If you too have experienced this problem then read on to learn how…


The client had a dataset which contained staff sales for various locations throughout the country:

Excel Unique Pivot Count_1

From this dataset they wanted to show total volume and amount of sales by location, and also the unique number of staff that had made a sale in each location.  At first attempt this is the closest they came:

Excel Unique Pivot Count_2

The PIVOT table can sum volume and sales by location but all it will do with a count of staff name is count every record in that field, so in the above you can see the total count of staff names is 7, the same number of records we have in the dataset – not what we need to answer the question.

To resolve this problem we need to add another column to the data to get our unique count of staff names.  This solution uses the SUMPRODUCT function, nested within an IF statement, to count the unique occurrences of a name, for each location.

With Staff Names starting in column A2 and locations starting in B2 the formula looks like this:

=IF(SUMPRODUCT(($A2:A$8=A2)*($B2:B$8=B2))>1,0,1)

*Note that my sample data range ends at row 8 hence the formula goes from A2:A8 and B2:B8 but make sure your entire range is covered.

Below you can see the example formula for each record along with its result:

Excel Unique Pivot Count_3

So from our new dataset with the additional column we are able to provide another PIVOT table solution that looks like this:

Excel Unique Pivot Count_4

Now that’s much better!

We are no longer counting every transaction but just summing the number of unique staff members making sales in each location.

We can do a quick sense check as we know in our original PIVOT table it showed a count of 4 for London, whereas now it shows 3.  A quick eye-ball of the data shows there are 4 records for London but only 3 staff members against it, Mr Bloggs, Mr Smith and Mr Anderson so we have achieved what we set out to.

Keep Excelling,

Please note that any data used in examples is dummy data used to duplicate the problem and illustrate a solution.  Client data is always confidential.

 

Share

Leave a Reply

You must be logged in to post a comment.