When you create a pivot table to summarize data, Excel automatically creates sums and counts for the fields that you add to the Values area. In addition, you might want to see a distinct count (unique count) for some fields, such as:
Excel Formula Training. Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges.
- The number of distinct salespeople who made sales in each region
- The count of unique products that were sold in each store
Normal Pivot Tables
For a normal pivot table, there isn’t a built-in distinct count feature in a normal pivot table. However, in Excel 2013 and later versions, you can use a simple trick, described below, to show a distinct count for a field.
For older versions of Excel, try one of the following methods:
- I'm looking for a formula calculating: distinct Count + multiple criteria Countifs does it but do not includes distinct count. Here is an example. I have a table on which I want to count the number of distinct items (column item) satisfying multiple conditions one column A and B: A2 and B.
- To count the number of unique values in a range of cells, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example show, the formula in F6 is: = SUMPRODUCT(1 / COUNTIF( B5:B14, B5:B14 )) With Excel 365, you can use a simpler and faster formula based on UNIQUE.
- Excel Details: To get the distinct count in the Pivot Table, follow the below steps: Right-click on any cell in the ‘Count of Sales Rep’ column.Click on Value Field Settings In the Value Field Settings dialog box, select ‘Distinct Count’ as the type of calculation (you may have to scroll down the list to find it). › Verified 3 days ago.
- In Excel 2010, use a technique to “Pivot the Pivot table”.
- In Excel 2007 and earlier versions, add a new column to the source data, and Use CountIf.
Add to Data Model – Excel 2013 and Later
In Excel 2013, if you add a pivot table’s source data to the workbook’s Data Model, it is easy to create a distinct count in Excel pivot table.
NOTE: This technique creates an OLAP-based pivot table, which has some limitations, such as no ability to add calculated fields or calculated items. If you need the restricted features, try the “Pivot the Pivot” method instead.
The Sample Data
Distinct Count In Excel Pivot Table Not Showing
In this example, there are 4999 records that show product sales, with the region and salesperson name. The first few records are shown in the screen shot below.
You can download the sample workbook from my Contextures website. On the Pivot Table Unique Count page, go to the Download section, and click the link.
Create the Pivot Table
First, to create a pivot table that will show a distinct count, follow these steps:
- Select a cell in the source data table.
- At the bottom of the Create PivotTable dialog box, add a check mark to “Add this data to the Data Model”
- Click OK
Set up the Pivot Table Layout
To set up the pivot table layout, follow these steps:
- In the pivot table, add Region to the Row area.
- Add these 3 fields to the Values area — Person, Units, Value
- The Person field contains text, so it defaults to Count of Person. The count shows the total number of transactions in each region, not a unique count of salespeople
Show a Distinct Count
To get a unique count (distinct count) of salespeople in each region, follow these steps:
How To Use Distinct Count In Excel
- Right-click one of the values in the Person field
- Click Value Field Settings
- In the Summarize Value Field By list, scroll to the bottom, and click Distinct Count, then click OK
The Person field changes, and instead of showing the total count of transactions, it shows a distinct count of salespeople names.
Distinct Count in Excel Pivot Table Workbook
To download the sample workbook, go to the Pivot Table Unique Count page, on my Contextures website. On that page, go to the Download section, and click the link.
That page also has instructions for calculating a unique count in older versions of Excel.
_________________________
Save
August 30, 2017 - by Bill Jelen
Excel Distinct Count or Unique Count. Pivot tables will offer a distinct count, if you check one tiny box as you create the pivot table.
Here is an annoyance with pivot tables. Drag the Customer column from the Data table to the VALUES area. The field says Count of Customer, but it is really a count of how many invoices belong to each sector. What if you really want to see how many unique customers belong to each sector?
Select a cell in the Count of Customer column. Click Field Settings. At first, the Summarize Values By looks like the same Sum, Average, and Count that you’ve always had. But scroll down to the bottom. Because the pivot table is based on the Data Model, you now have Distinct Count.
After you select Distinct Count, the pivot table shows a distinct count of customers for each sector. This was very hard to do in regular pivot tables.
Count Distinct in Excel 2010
Use Distinct Count In Excel Function
To join two tables in Excel 2010, you have to download the free Power Pivot add-in from Microsoft. Once you have that installed, here are the extra steps to get your data into Power Pivot:
- Select a cell in the Data table. On the PowerPivot tab, choose Create Linked Table. If Excel leaves you in the PowerPivot grid, use Alt + Tab to get back to Excel.
- Select a cell in the Sectors table. Choose Create Linked Table.
- From either the PowerPivot tab in the Excel ribbon or the Home tab in the PowerPivot ribbon, choose to create a pivot table.
Use Distinct Count In Excel Pivot Table
When it comes time to create relationships, you have only one button called Create. Excel 2010 will attempt to AutoDetect relationships first. In this simple example, it will get the relationship correct.
Thanks to Colin Michael and Alejandro Quiceno for suggesting Power Pivot in general.
Watch Video
- Introduced the Data Model in Podcast 2014 for Joining Tables
- Another Benefit is the ability to do Distinct Count
- Regular pivot table can not count customers per sector
- Add the data to the Data Model and you have Distinct Count available
- Before Excel 2013, you would have to add 1 / COUNTIF to the original data
Video Transcript
Learn Excel from MrExcel podcast, episode 2015 - Distinct Count!
Alright, all the tips in this book are going to be podcast, check out this playlist for the whole set!
OK, so today we have to create a report that shows how many customers are in each sector, and a regular Pivot table CANNOT do this. So Insert PivotTable will put sectors down the left-hand side, and then ask for the count of Customer, and it says it's giving us the count of Customer. But this isn't the count of Customer, this is how many records there are, alright, we don’t have 563 customers, completely, completely useless. But check this out, amazingly easy to solve this, yesterday's podcast we talked about using the Data Model to join 2 tables together. Today I just have one table, there's, you know, you wouldn't think there's any reason to use the data model, except for this, so choose the box “Add this data to the Data Model”.
By the way, this is brand new in Excel 2013, so you need 13 or 16, if you're stuck on a Mac or back in Excel 2010, I'll show you the old solution here at the end. Click OK, build the exact same report, sectors down the left-hand side, count of Customer, an exact same wrong answer, but here's the difference. When we come into Field Settings see, it looks the same, Sum, Count, Average, Max, Min, there's a few missing, and at the very bottom there's a new one called Distinct Count. Wow, that is something that has been so hard to do in old versions of Excel, in fact let me show you how we used to do it in Excel 2010.
Use Distinct Count In Excel 2013
So here's the data, you would have to come out and do a COUNTIF, count how many times Vertex42 appears in column D, and it appears there are 6 times. So then the Distinct Count is =1 divided by that, alright, see what we're doing, if there's six records with Vertex42, we're giving each of them 1/6 or 0.16611, and when we add all that up, that will get us to 1, right? There's 5 records here, each gets 1/5 or 20%, add all those up, each of those gets us to 1. So back in Excel 2010 or 7 or 3 or wherever you are, you don't have the Data Model, so you add those extra fields there, Sector, and then Distinct Count. This was so much more difficult than the new way, so I certainly appreciate the data model for this one. Well this tip, and a lot more in the book, click the “i” on the top-right hand corner, you can buy the book, $25 in print, $10 for an e-book, it's cheap!
Use Distinct Count In Excel Spreadsheet
In yesterday's podcast, 2014, we talked about the Data Model for joining tables, another benefit is the ability to do a distinct count. Regular Pivot table cannot count customers per sector, add the data to the Data Model, and you have Distinct Count available. Before Excel 2013, you do 1/COUNTIF in the original data, and of course, if you want to do distinct count for something else, you might have to change that formula, really, really frustrating. Beautiful, beautiful side benefit of the whole Power Pivot engine!
Use Distinct Count In Excel Using
Download File
Download the sample file here: Podcast2015.xlsx
Title Photo: masch / pixabay