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.
Table of Contents hide
Download Practice Workbook
3 Ways to Count Unique Values Using Excel Pivot Table
1. Counting Unique Values with Helper Column
1.1 Using COUNTIF Function
1.2 Combining IF and COUNTIF Functions
1.3 Applying IF and SUMPRODUCT Functions
2. Count Unique Values Without Helper Column
3. Utilizing PowerPivot Tool
How to Count Unique Values with COUNTA and UNIQUE Functions in Excel
How to Enable the Missing Distinct Count Option of Pivot Table in Excel
Practice Section
Conclusion
Further Readings
Download Practice Workbook
Counting Unique Values Excel Pivot.xlsx
Using PowerPivot.xlsx
3 Ways to Count Unique Values Using Excel Pivot Table
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.
- Third, 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.
Similar Readings:
- How to Use COUNTIF for Unique Text (8 Easiest Ways)
- COUNTIFS Unique Values in Excel (3 Easy Ways)
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.
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.
Conclusion
In short, this tutorial explores all the ins and outs of how to count unique values using 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. Or, you can check out our other articles related to Excel functions on ExcelDemy.
Further Readings
- How to Count Unique Names in Excel (5 Methods)
- Count Unique Values with Criteria by SUMPRODUCT in Excel
FAQs
How do you count unique values based on multiple criteria? ›
You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears.
How do you find the top 3 values in a pivot table? ›- Create a new pivot table on the same worksheet.
- Add the Name field to the Rows area.
- Add the Score field to the Values area.
- Rename the Score field from "Sum of Score" to "Score " (note trailing space)
- Filter values to show "Top 3 items by Score"
- Set sort to "Descending by Score"
...
Count how often multiple values occur by using a PivotTable
- In the Summarize value field by section, select Count.
- In the Custom Name field, modify the name to Count.
- Click OK.
To count unique values in Excel in this situation, combine the SUM, IF, and COUNTIF functions. 1. Enter the formula =SUM(IF(COUNTIF(range, range)=1,1,0)) in the desired cell to count unique values. The initial cell and the final cell are indicated by the range.
Is there a count distinct function in Excel? ›To count distinct values in excel, first enter the formula =SUM(1/COUNTIF(range, range)) in the desired cell. The range specifies the starting cell and ending cell separated by a colon. This is an array function, so press Ctrl+Shift+Enter to apply the formula.
How to extract unique values based on multiple criteria in Excel? ›Get a list of unique values based on criteria. To extract unique values with condition, use the Excel UNIQUE and FILTER functions together: The FILTER function limits the data only to values that meet the condition. The UNIQUE function removes duplicates from the filtered list.
How do you find 3 highest values in Excel? ›If you need to find the top three numbers in a list on your spreadsheet, you can easily find the largest and the smallest number using the MAX and MIN function.
How do you find best 3 in Excel? ›- Formula: = AVERAGE(LARGE(C3:G3,{1,2,3}))
- =AVERAGE(number1, [number2],...)
- =LARGE(array, k)
- =AVERAGE(LARGE(C3:G3,{1,2,3}))
- Click any cell on the worksheet.
- Select Insert, and then the down arrow under PivotTable.
- Select From External Data Source.
- Select Choose Connection.
- On the Tables tab, in This Workbook Data Model, select Tables in Workbook Data Model.
Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5.
How do I count multiple occurrences in Excel? ›
You can use the =UNIQUE() and =COUNTIF() functions to count the number of occurrences of different values in a column in Excel.
How do you use Countif criteria? ›- Select a cell.
- Type =COUNTIF.
- Double click the COUNTIF command.
- Select a range.
- Type ,
- Select a cell (the criteria, the value that you want to count)
- Hit enter.
Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table. Display all records from the table using select statement.
What is a distinct count? ›Count is the total number of values. Count Distinct in the number of unique values. Count Distinct will always be equal to or less than Count. 1 Helpful. Share.
How do I extract unique values from a list? ›As we want to extract unique items from a list, we need to search and look for the value 0. This will work when we use the MATCH function. It provides the comparative positioning of the initial equivalent value of the array. Depending on this result, the INDEX formula will extract unique items from a list.
How do you use data validation for unique values in Excel? ›- Click the Data tab, then the Data Validation menu and select Data Validation.
- Click the Allow: drop-down button and select Custom as Validation criteria.
- Enter the following in the Formula bar: =COUNTIF(C$3:C$7,C3)<2.
- STEP 1: Drag Sales to Values. This will default to become Sum of SALES.
- STEP 2: Click on the arrow beside Sum of SALES and select Value Field Settings.
- STEP 3: Select Max under Summarize value field by. Click OK.