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 Unsplash.com

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.

Note

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 Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.