Excel PivotTables Unique Count 3 Ways

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:

1. 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:

=IF(COUNTIF($B$2:B2,B2)=1,1,0)

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:

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

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.

1. Insert a PivotTable from within PowerPivot.
2. Right click on the table name in the PowerPivot Field list and select ‘Add New Measure…’.

   

3. Give your Measure a name (this is the field name that will appear in your PowerPivot field list).
4. Enter your formula =COUNTROWS(DISTINCT(‘Product Raw Data’[Product]))

Voila, you have a unique count of your products:

3. Excel 2013 PivotTable Distinct Count

Now, I’d like to show you step by step instructions on how to count unique items in Excel 2013 but after installing it a while ago I found that if I wanted to open an Excel file attachment from Outlook, (by double clicking on the file in the email) it would open it in Excel 2013. Makes sense since it’s the most up to date version, but I didn’t want to.

Call me old fashioned, but I wanted to keep opening them in 2010 (because most of the questions I get are about Excel 2007 or 2010), and despite considerable Googling I couldn’t find a way to change the default for Excel back to 2010.

The workaround was to save the file first and then open it from within Excel 2010 but this isn’t ideal. You see I get a lot of emails (a lot!) and I don’t want to have to save every file I get just so I can have a quick look at it.

So, I did the only thing I knew would work and I uninstalled Excel 2013.

I’ll get around to installing it again sometime soon but for now you can read this post by Michael Alexander on how to count distinct records in Excel 2013 using PivotTables.

What Do You Think?

Did you like this? Let me know by sharing it using the social media icons below, or leave a comment.

Tagged as: excel pivot tables, pivot table, pivot tables