# How to Count Unique Values Using Excel Pivot Table (3 Ways) (2023)

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.

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

(Video) Count Unique Values in PivotTables in Excel

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

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)`

(Video) 2 Ways to Calculate Distinct Count with Pivot Tables

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.

(Video) Easy way to get "Distinct Count" in Pivot Tables #### 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. • 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. (Video) How to add unique count to a pivot table

• 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.

(Video) Get a Unique Count of Items in a Pivot Table ## 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.

• 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? ›

Summary
1. Create a new pivot table on the same worksheet.
2. Add the Name field to the Rows area.
3. Add the Score field to the Values area.
4. Rename the Score field from "Sum of Score" to "Score " (note trailing space)
5. Filter values to show "Top 3 items by Score"
6. Set sort to "Descending by Score"

How do I count the number of occurrences in Excel using a pivot table? ›

Enter the following data in an Excel spreadsheet. Click Insert > PivotTable.
...
Count how often multiple values occur by using a PivotTable
1. In the Summarize value field by section, select Count.
2. In the Custom Name field, modify the name to Count.
3. Click OK.

What is the formula to count unique values? ›

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? ›

How to Calculate the Average Top 3 Scores in Excel
1. Formula: = AVERAGE(LARGE(C3:G3,{1,2,3}))
2. =AVERAGE(number1, [number2],...)
3. =LARGE(array, k)
4. =AVERAGE(LARGE(C3:G3,{1,2,3}))

How do I link 3 pivot tables? ›

Use the Data Model to create a new PivotTable
1. Click any cell on the worksheet.
2. Select Insert, and then the down arrow under PivotTable.
3. Select From External Data Source.
4. Select Choose Connection.
5. On the Tables tab, in This Workbook Data Model, select Tables in Workbook Data Model.

How do I count the number of specific values in a column in Excel? ›

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? ›

How to use the =COUNTIF function:
1. Select a cell.
2. Type =COUNTIF.
3. Double click the COUNTIF command.
4. Select a range.
5. Type ,
6. Select a cell (the criteria, the value that you want to count)
7. Hit enter.

Can you use count with distinct? ›

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? ›

Allow unique values only
1. Click the Data tab, then the Data Validation menu and select Data Validation.
2. Click the Allow: drop-down button and select Custom as Validation criteria.
3. Enter the following in the Formula bar: =COUNTIF(C\$3:C\$7,C3)<2.

How do I filter pivot by highest value? ›

Maximum in Excel Pivot Tables
1. STEP 1: Drag Sales to Values. This will default to become Sum of SALES.
2. STEP 2: Click on the arrow beside Sum of SALES and select Value Field Settings.
3. STEP 3: Select Max under Summarize value field by. Click OK.
Dec 31, 2021

## Videos

1. Excel Distinct Count with a PivotTable
(Chris Menard)
2. Counting in Excel Part7 - Counting unique values using pivot table
(CRC SOFTWARE TUTOR)
3. Pivot Table Count Unique Values Excel 2010
(Computer Tutoring)
4. Excel - Do a Count of Unique Values | Text and/or Number | Get a Count of Unique Values in Excel
(Chester Tugwell)
5. Finding Unique Values with a Pivot Table
(Watchistan)
6. How to Count the Number of Unique Values in a List in Excel : Using Excel
(eHowTech)
Top Articles
Latest Posts
Article information

Author: Sen. Ignacio Ratke

Last Updated: 06/02/2023

Views: 6518

Rating: 4.6 / 5 (76 voted)

Author information

Name: Sen. Ignacio Ratke

Birthday: 1999-05-27

Address: Apt. 171 8116 Bailey Via, Roberthaven, GA 58289

Phone: +2585395768220