It’s easy to get a sum in a pivot table, or a total count. But how can you count unique items in an Excel pivot table?
For example, if you’re analyzing sales data, you might need to show these types of counts:
–How many unique products were sold in each store?
–How many distinct people made sales in each region?
Distinct Count Calculation
In the screen shot below, the pivot table shows the distinct sales rep counts. For example, there are:
- 4 sales people in the mountain region
- 7 sales people in the south
Video: Get Distinct Count in Pivot Table
In Excel 2013, there’s a quick and easy to create a unique count in a pivot table.
This short video shows the steps to show a distinct count with the Excel Data Model, and there are written steps and screen shots, on the Count Unique Items in Pivot Table page on my Contextures website.
- Tip: Be sure to read the Limitations section, below the video.
Data Model Limitation
When you build a pivot table with the Data Model method, it creates an OLAP-based pivot table.
That creates limitations in the pivot table, such as:
- You can’t group the pivot table items
- You can’t add calculated fields or calculated items
More about OLAP
On the Microsoft website, there are more details on OLAP (Online Analytical Processing) features and limitations.
That might help you decide whether to use this method, or choose a different method.
Other Ways to Get Distinct Count
If the Data Model method won’t work for you, there are other ways to get a unique count in a pivot table.
The following methods are described on the Count Unique Items in Pivot Table page on my Contextures website:
- In Excel 2010, use a “Pivot the Pivot table” technique.
- In Excel 2007, add COUNTIF formula column to the source data
- OR, if you have the Power Pivot add-in installed, use it to get distinct count
- Note: There’s a Power Pivot video, further down the page
There’s also a comparison of the calculation times for the different methods
Get the Sample File
To get the Count Distinct Items sample file, and to see other ways to get a pivot table distinct count, go to the Count Unique Items in Pivot Table page on my Contextures website.
Video: Count Distinct with Power Pivot Add-in
This video shows the steps for creating a Power Pivot pivot table, and adding a field with the unique count.
For written instructions, see the blog post with Excel 2013 steps.
And if you need them, there are instructions for Excel 2010 too.
__________________
Quick Count Unique (Distinct) Items in Excel Pivot Table
__________________