Count Unique Values in a Pivot Table

Using a Data Model with a Pivot Table

The data model is yet another thing I love about the newer versions of Microsoft Excel. If you are using Excel for Microsoft 365, Excel 2019, Excel 2016, and Excel 2013, you have the access to Data Model.

1. To start with, click on any cell in the data and go to the “Inset” tab in your ribbon.
   
2. Here click on the pivot table and a dialogue box appears.
   
3. Now tick mark the box at the bottom of the dialogue box, “Add this data to the Data Model” and hit OK.
4. After this, you will get a usual pivot table and arrange your data in the pivot table fields as you did earlier. This will give you the same pivot table you had earlier, but the pivot table fields look a little different.
   
5. Here’s the trick: click on the small arrow next to “Count of Service provider” in the Pivot Table fields.
   
6. After this click on the “Value Field Settings”.
   
7. Now scroll down to the end to get “Distinct Count” and click OK.
   
8. Here we go: you have a Distinct/Unique Count for each region in the pivot table.
   

Therefore, we have only 18 unique service providers in the country.

Using the Function COUNTIF

Another approach to calculating the Unique entries is simply using the formula COUNTIF in your Datasheet.

• Let’s start by adding a column to your data with a header of your choice. Here we will call it “Count No.”
• Add this formula (=IF (COUNTIF ($B$2: B2, B2)>1,0,1)) to the cell D2 and drag it till the end.

How this Formula Works??

First of all, we have fixed the start point of the range, also called Absolute i.e $B$2. This means it will not change even if you drag your formula downwards. Now when you drag the formula downwards to D3, this formula becomes IF(COUNTIF($B$2:B3,B3)>1,0,1)

Read it like

Countif ($B$2:B3,B3) will give you the number of times B3 exists between the range $B$2:B3. IF function is used to add a condition: IF ((the number of times B3 Exists in a given range) is greater than 1, then give 0 else return 1)

Now, if the name in the given column exits more than 1 time, the formula will return you 0 else you will get 1. Therefore, for all those repeated names you will get 0 in the column Count no.

1. Now create a Pivot Table with your data.
   
2. Here you need to add Location to the ROWS and Count No to the values.
   
3. Boom!! The Pivot Table is ready with unique entries in each Pivot Table.
   

Use Power Pivot to Count Unique Values

Here comes the most powerful method to identify the unique entries; Power Pivot. Make sure that you got the Power Pivot tab in your ribbon. If you cannot find the tab, check out this tutorial.

1. As said earlier, first of all, make sure that the Power Pivot tab is enabled.
   
2. After that go to the Data model and click on the Manage button.
   
3. Here you will get a window opened, which surely will be blank in case this is the first time you are importing the data.
   
4. Click on Home → Get External Data
   
5. Here you will find multiple options and sources available to upload the data. But we need to upload a simple excel. So follow the steps and screenshots and click on “From Other Sources”.
   
6. Now you will again get a dialogue box opened. Scroll to the end to get the option of Excel File and click Next.
   
7. Here you can rename the connection from the default name “Excel”. Click on Browse to choose a path to your data file.
   
8. Also, if you want the top column to be the header row, tick mark the option “Use the first row as column header” and hit Next.
   
9. In the end, the file is imported to the data model and click on finish.
   
10. Here we go: that’s a success with all the 28 rows imported. Now, hit on close.
    
11. Now, this is how it looks like.
    
12. From here we will create a Pivot Table by Home → Pivot Table
    
13. Since we have the data in sheet 1, we will expand the columns by clicking on the small triangle next to it.
    
14. Now, place the Location on the Rows and the Service providers on the values as we did earlier. This will give a simple Pivot Table with the total number of service providers.
    
15. Here’s the trick. Now go to the PowerPivot window and click on Measure to get the option of New measure.
    
16. Now add a description of the desired name and start typing the formula in the formula section.
    
17. As you start typing, you will get the suggestions automatically. Here we need the function of distinct count. Select the distinct count function.
    
18. After this, press the tab button or start a ( bracket and select the column for which we need the distinct count. Like here we need the distinct count of Service providers. Hence, our formula will look like this =DISTINCTCOUNT(Sheet1[Service Provider]))
    
19. In the end, select the category. Since we are finding out the unique count of the service providers, we will select the category as “Numbers”.
    
20. Change the Format to “Whole number” and hit OK. Another column will be added to the Pivot Table which will give you the unique entries.