PivotTables are the Origamists of Excel. Folding and summarising data into almost any shape. I say 'almost' because until Excel 2013 they couldn’t calculate a unique (sometimes called distinct) count.
For example, you might want to count the number of 'things' you had, whether it be products or customers, or any other unique combination of records you might want to count.
In the example below we can see the count for each product, but this is actually the count of the number of records for each product, as opposed to the number of unique products.
Bummer. You'd think something so simple would be available. Not to worry. Let's take a look at 3 different ways we can count unique items in a PivotTable.
The idea being that you'll find a solution that works with the version of Excel you use.
1. Excel PivotTable Count Unique Items pre 2013
Pre Excel 2013 you need to use a workaround, which is this:
- Add a helper column to your raw data with a formula that counts a 1 for the first instance of the product, and a zero for any duplicates.
In cell E2 I entered this formula:
Then copied it down the column.
Now I can include a sum of my new ‘Distinct’ column in my PivotTable like this:
And below I further summarised the Products into their Categories which shows that I have 77 unique products, with the highest number being in the Confections category:
Remember the 'Count of Product' is actually the count of records (in this case they're sales) for each product.
If you had multiple fields you wanted to combine to count unique combinations you could use the COUNTIFS function. As in the example below where I want to count unique combinations of the Region and Business Unit.
Or if you have Excel 2003 you could use a SUMPRODUCT function like this:
You could then sum your unique Region/Business Unit combinations like this:
2. Excel 2010 PowerPivot Distinct Count
If you’ve got the free Excel 2010 PowerPivot addin installed you can go ahead and try this method.
- Insert a PivotTable from within PowerPivot.
- Right click on the table name in the PowerPivot Field list and select ‘Add New Measure…’.
- Give your Measure a name (this is the field name that will appear in your PowerPivot field list).
- Enter your formula =COUNTROWS(DISTINCT('Product Raw Data'[Product]))
Voila, you have a unique count of your products:
3. Excel 2013 PivotTable Distinct Count
What Do You Think?
Did you like this? Let me know by sharing it using the social media icons below, or leave a comment.