MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Count Distinct

June 17, 2019 - by Bill Jelen

Count Distinct. Photo Credit: Monika Grabkowska at

To see 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 the invoices belong to each sector. What if you really want to see how many unique customers belong to each sector?

This report showing Count of Customer by Sector is really giving you the number of orders in each sector.

The trick is to build your pivot table using the Data Model. Double-click the Count of Customer heading. At first, the Summarize Values By offers choices such as Sum, Average, and Count. Scroll down to the bottom. Because the pivot table is based on the Data Model, you now have Distinct Count.

Double click the heading for Count of Customer. Using the Summarize Values By tab, scroll all the way down to the bottom and a secret extra choice appears: 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.

The report now correctly shows the number of distinct customers in each sector.


If you don't have Excel 2013 or newer and do not have the Data Model (for example, you might be using Excel for Mac), there are other ways to get a distinct count. Read Roger Govier's article here.

Thanks to Colin Michael and Alejandro Quiceno for suggesting Power Pivot.

Title Photo: Monika Grabkowska at

Bill Jelen is the author / co-author of
Excel Insights – A Microsoft MVP Guide to the Best Parts of Excel

There are fewer than 100 Excel MVPs worldwide. 24 of them have contributed to this book. Written, edited, reviewed and printed by Excel MVPs, this is practical Excel passion undiluted, with each MVP highlighting some of their favorite topics.