How to do a Distinct Count or a Unique Count in an Excel 2013 Pivot Table. If you still have Excel 97-2010, watch the outtake for the ugly method prior to Excel 2013.
Transcript of the video:
The MrExcel podcast is sponsored by eduPOW – home of the $5 Courses.
Check out my Excel class: Excel formulas - The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel from MrExcel podcast, episode 1924.
Distinct Count in Excel 2013.
Pivot Table.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
One of the things we've always wanted to do in Pivot Tables, is get a Distinct Count.
And this was always impossible, or very, very hard in Excel 2007 and earlier.
You know, you would have to add a function on here that’ll do =1/COUNTIF of the number of customers.
And in Excel 2010, you could download the PowerPivot, but now, in Excel 2013, it is so much easier: INSERT, PivotTable, and there's an extra check box here, to “Add this data to the DataModel”.
Now, normally we would do this, when we have two tables to join, but it just makes this whole problem absolutely beautiful.
Over here, on the right hand side, I'm going to take Sector and drag it to ROWS.
And then I'm going to take Customer and drag it to VALUES, and we get a count.
But that's not the count of unique customers or distinct customers, that's the number of orders that came in, the number of total records.
And that's not what we want, we want the distinct customers.
I'm going to choose that field, go to Field Settings and here, on Summarize Values By, because the data is in the data model, we have this extra choice, that we don't have in regular Pivot Tables or in Excel 2010 Pivot Tables Distinct Count.
Bam, there you go, absolutely amazing and easy, easy to do.
It's been such a problem for so long, so Excel 2013 and the Data Model solved this.
I can already hear the comments at YouTube: well, wait, I don't have Excel 2013 yet.
What… What are you waiting for, it's almost the end of 2014.
Excel 2015 is almost here, it's time to go, let's upgrade.
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast – MrExcel.
Okay, okay… For those of you, that are still back in Excel 2010 or Excel 97, or whatever it is… =COUNTIF($D$2:$D$546,D2) - count if how many times the customers in D2 to D564.
Press F4, is equal to D2.
So Ford is in there 56 times.
Edit that formula to =1/ COUNTIF($D$2:$D$546,D2).
And let's say, we don't want the date there, we just want a number.
Double-click to shoot that down, then Insert, PivotTable, OK, put Sector down the left hand side.
And then Distinct, Sum of Distinct in Values, and you'll get the same answer.
But it's very, very inflexible, seriously, Office 365 Home Premium, $9/month, come on…
Check out my Excel class: Excel formulas - The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel from MrExcel podcast, episode 1924.
Distinct Count in Excel 2013.
Pivot Table.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
One of the things we've always wanted to do in Pivot Tables, is get a Distinct Count.
And this was always impossible, or very, very hard in Excel 2007 and earlier.
You know, you would have to add a function on here that’ll do =1/COUNTIF of the number of customers.
And in Excel 2010, you could download the PowerPivot, but now, in Excel 2013, it is so much easier: INSERT, PivotTable, and there's an extra check box here, to “Add this data to the DataModel”.
Now, normally we would do this, when we have two tables to join, but it just makes this whole problem absolutely beautiful.
Over here, on the right hand side, I'm going to take Sector and drag it to ROWS.
And then I'm going to take Customer and drag it to VALUES, and we get a count.
But that's not the count of unique customers or distinct customers, that's the number of orders that came in, the number of total records.
And that's not what we want, we want the distinct customers.
I'm going to choose that field, go to Field Settings and here, on Summarize Values By, because the data is in the data model, we have this extra choice, that we don't have in regular Pivot Tables or in Excel 2010 Pivot Tables Distinct Count.
Bam, there you go, absolutely amazing and easy, easy to do.
It's been such a problem for so long, so Excel 2013 and the Data Model solved this.
I can already hear the comments at YouTube: well, wait, I don't have Excel 2013 yet.
What… What are you waiting for, it's almost the end of 2014.
Excel 2015 is almost here, it's time to go, let's upgrade.
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast – MrExcel.
Okay, okay… For those of you, that are still back in Excel 2010 or Excel 97, or whatever it is… =COUNTIF($D$2:$D$546,D2) - count if how many times the customers in D2 to D564.
Press F4, is equal to D2.
So Ford is in there 56 times.
Edit that formula to =1/ COUNTIF($D$2:$D$546,D2).
And let's say, we don't want the date there, we just want a number.
Double-click to shoot that down, then Insert, PivotTable, OK, put Sector down the left hand side.
And then Distinct, Sum of Distinct in Values, and you'll get the same answer.
But it's very, very inflexible, seriously, Office 365 Home Premium, $9/month, come on…
