Unquestionably, Microsoft Excel excels at crunching numbers! Now, this means that you can perform tedious operations like counting text cells, counting unique, counting duplicates, etc. in the blink of an eye. In this regard, Excel becomes a convenient and valuable tool. Keeping this in mind, this article demonstrates 3 ways of how to count unique values using Excel Pivot Table. In addition, we’ll also explore how to count unique values with Excel functions and enable the missing distinct count aggregation in Excel.
Count Unique Values Using Excel Pivot Table: 3 Ways
First and foremost, let’s consider the List of Movies dataset in the B4:D24 cells containing the name of the “Movie”, “Actor”, and “Release Year” respectively. Here, we want to obtain a unique count of the “Actors” acting in each of the movies. Therefore, let’s see each method on how to count unique values using Excel Pivot Table with the appropriate illustrations.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
1. Counting Unique Values with Helper Column
First of all, we’ll count unique values by inserting a helper column and then use the PivotTable to compute the occurrence of each unique value. So, let’s see the process in action.
1.1 Using COUNTIF Function
At the very beginning, we use the COUNTIF function to count the range of cells and return the instances of the given condition.
📌 Steps:
- First, go to the E5 cell >> enter the formula given below.
=COUNTIF($C$5:$C$24,C5)
Here, the C5:C24 range refers to the “Actors” and the C5 cell indicates the “Hugh Jackman”.
Formula Breakdown
- COUNTIF($C$5:$C$24,C5) → counts the number of cells within a range that meet the given condition. Here, the C5:C24 cells represent the range argument that refers to the “Actor”, while the C5 cell indicates the criteria argument that returns the count of the matched value.
- Output → 4
📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.
- Second, move to the B5 cell >> navigate to the Insert tab >> click on PivotTable >> click the New Worksheet radio button >> hit OK.
- Next, drag the Actor and Helper Column fields into the Rows and Values areas respectively.
- Then, right-click on any of the column headers >> press the Value Field Settings.
- Then, choose the Count option from the list.
Boom! That is how simple it is to count unique values using an Excel PivotTable.
1.2 Combining IF and COUNTIF Functions
Furthermore, we can also combine the popular IF and COUNTIF functions to return the count of unique values in Excel.
📌 Steps:
- Initially, jump to the E5 cell >> type in the equation given below.
=IF(COUNTIF($C$5:$C$24,C5)>0,1)
Formula Breakdown
- IF(COUNTIF($C$5:$C$24,C5)>0,1) → becomes
- IF(4>0,1) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, 4>0 is the logical_test argument which prompts the IF function to return 1 (value_if_true argument) otherwise it returns Blank (value_if_false argument).
- Output → 1
- Afterward, follow the steps shown in the prior method to insert the PivotTable >> drag the fields into their respective locations.
Finally, the results should look like the image shown below.
1.3 Applying IF and SUMPRODUCT Functions
Besides, another way involves applying the IF and SUMPRODUCT functions to calculate the count of unique values.
📌 Steps:
- To begin with, insert the following expression into the E5 cell.
=IF(SUMPRODUCT(($B$5:$B5=B5)*($C$5:$C5=C5))>1,0,1)
Formula Breakdown
- SUMPRODUCT(($B$5:$B5=B5)*($C$5:$C5=C5)) → returns the sum of the products of the corresponding ranges or arrays. Here, the ($B$5:$B5=B5)*($C$5:$C5=C5) is the array1 argument where the value of the B5 and C5 cells are multiplied to give the output.
- Output → 1
- IF(SUMPRODUCT(($B$5:$B5=B5)*($C$5:$C5=C5))>1,0,1) → becomes
- IF(1>1,0,1) → here, 1>1 is the logical_test argument which prompts the IF function to return 1 (value_if_true argument) else it returns 0 (value_if_false argument).
- Output → 1
- Likewise, follow the steps shown previously to generate the output shown in the picture below.
2. Count Unique Values Without Helper Column
Alternatively, we can also bypass the process of inserting helper columns and count unique values using PivotTable in Excel.
📌 Steps:
- In the first place, proceed to the B5 cell >> click on Insert >> select PivotTable >> enable the New Worksheet option >> check the Add this data to the Data Model option.
- Following this, insert the Movie and Actor fields into the Rows and Values areas.
- In turn, right-click on the column header >> go to Value Field Settings.
- Later, select the Distinct Count option >> hit the OK button.
Lastly, the final output should appear in the screenshot below.
3. Utilizing PowerPivot Tool
Conversely, we can also employ the PowerPivot Add-in of Excel to count unique values. Now, to do this, copy the data from the “Dataset” worksheet and paste it into the A1 cell of the “Dataset for PowerPivot” worksheet, as shown in the figure below.
📌 Steps:
- In the first place, open a new workbook, in this case, “Using PowerPivot.xlsx” workbook >> move to PowerPivot >> click on the Manage option.
- Not long after, press the From Other Sources option >> choose Excel File >> hit Next.
- Now, browse for the Excel file containing the “Dataset for PowerPivot” worksheet >> check the option to Use first row as column headers >> press Next.
- In turn, a list of all the worksheets appears >> select the “Dataset for PowerPivot” worksheet >> click on Finish.
- Later, select the PivotTable option from the drop-down.
- Then, in the PowerPivot tab, add a New Measure by clicking the New Measure drop-down.
- Following this, enter a Measure name, for instance, “Count of Movies” >> copy and paste the formula given below >> choose the Number category >> format as Whole Number.
=DISTINCTCOUNT([Movie])
- Eventually, click on the “Count of Movies” measure and the final output looks like the image given below.
Read More: Count Rows in Group with Pivot Table in Excel
How to Count Unique Values with COUNTA and UNIQUE Functions in Excel
For one thing, we can obtain the count of the total number of unique values present in an array using the COUNTA and UNIQUE functions. Here, the UNIQUE function yields all the unique values in the given array, and the COUNTA function counts the non-blank instances.
📌 Steps:
- To start with, enter the F5 cell >> insert the expression into the Formula Bar.
=COUNTA(UNIQUE(C5:C24))
For example, the C5:C24 range refers to the “Actor” column.
Formula Breakdown
- UNIQUE(C5:C24) → returns the unique values from a range or array. Here, the C5:C24 is the array argument that refers to the “Actor” column.
- Output → {“Hugh Jackman”;”Christian Bale”;”Johnny Depp”;”Tom Cruise”;”Leonardo DiCaprio”}
- COUNTA(UNIQUE(C5:C24)) → counts the number of cells in a range that are not empty. Here, the UNIQUE(C5:C24) is the value1 argument that refers to the array returned by the UNIQUE function
- Output → 5
How to Enable the Missing Distinct Count Option of Pivot Table in Excel
Last but not least, if you’re having trouble finding the Distinct Count option in the PivotTable, then the following section can help you solve this issue. Therefore, just follow the steps.
📌 Steps:
- At the very beginning, insert a PivotTable as usual >> next, insert a check beside the Add this data to the Data Model option.
- Afterward, open the Value Field Settings >> you’ll find the Distinct Count in the list.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Download Practice Workbook
Conclusion
In short, this tutorial explores all the ins and outs of how to count unique values using the Excel Pivot Table. Now, we hope all the methods mentioned above will prompt you to apply them in your Excel spreadsheets more effectively. Furthermore, if you have any questions or feedback, please let me know in the comment section.
Count Unique Values Using Excel Pivot Table without Helper Column was very helpful, thank you! This was a very simple and easy way to get the counts I needed. (Senior Analyst, Reporting and Metrics, Cardinal Health)
Thanks for commenting Merle, glad to hear that it helped you.
This feature doesn’t exist, i’m on the latest excel and its not there.
Bob, PivotTable is one of the prime features of Excel, so regardless of the version (contemporary versions) you should get it inside the “Insert” tab of the ribbon. But if you don’t find that there you may need to customize your ribbon. Click on File > Options, then follow the image
And if counting unique is your main goal right now you might get that using the UNIQUE function (and COUNT family function for counting).