MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Distinct Count

August 30, 2017 - by Bill Jelen

Distinct Count

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?

Pivot Table
Pivot Table
Distinct Count
Distinct Count

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.

The Result
The Result

Count Distinct in Excel 2010

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:

  1. 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.
  2. Select a cell in the Sectors table. Choose Create Linked Table.
  3. From either the PowerPivot tab in the Excel ribbon or the Home tab in the PowerPivot ribbon, choose to create a 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2015 distinct count all right all the
  • tips in this book are going to be
  • podcast check out this playlist for the
  • whole set okay 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 pivot table will put sectors down
  • 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 kind of customer this is how
  • many records there are all right we're
  • not 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 two 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 and 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 exact same wrong
  • answer but here's the difference when we
  • come into field settings see it looks
  • the same some count average maximum
  • there's a few missing and at the very
  • bottom there's a new one called distinct
  • account 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 so here's
  • the data you would have to come out and
  • do a count if count how many times
  • vertex 42 appears in column D and it
  • appears there are six times so then the
  • distinct count is equal 1 divided by
  • that alright see what we're doing if
  • there's 6 records with vertex 42 we're
  • giving each of them 160 point one six
  • six seven and when we add all that up
  • that will get us to one right there's
  • five records here each gets one fifth or
  • twenty percent add all those up each of
  • us each of those gets us to one so back
  • in Excel 2010 or seven or three 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 in a lot more in
  • the book click the eye on the top right
  • hand corner you can buy the book 25
  • bucks in print ten bucks for an e-book
  • it's cheap 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 can not count customers per
  • sector add the data to the data model
  • and you have to stink count available
  • before Excel 2013 you do one / count if
  • in the original data and of course if
  • you want to do is this encounter
  • something else you might have to change
  • that formula really really frustrating
  • beautiful beautiful side benefit of the
  • whole power pivot engine

Download File

Download the sample file here: Podcast2015.xlsx

Title Photo: masch / pixabay

Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.