Nuraida Kashmin
Sometimes we need to count the duplicates in an Excel pivot table for easy calculation. The Excel pivot table is an amazing feature. Counting duplicates is also known as distinct count here. In this article, we are going to learn how to do that with some beautiful examples and explanations.
Practice Workbook
Download the following workbook and exercise.
2 Easy Ways to Count Duplicates in Excel Pivot Table
1. Count Duplicates by Inserting a Helping Column in Excel Pivot Table
Inserting a helping column is the most available way to count duplicates in the Excel pivot table. Assuming, we have a dataset (B4:E10) of Employees based on their locations, sold products, and amount of sold products. We are going to find out the total number of employees who work there based on their locations.
Step 1:
- First, insert a helper column, ‘Count D’ in the F4:F10 range.
- Next, select Cell E5.
- Now type the formula:
=IF(COUNTIFS($C$5:C5,C5,$B$5:B5,B5)>1,0,1)
Step 2:
- Press Enter and use the Fill Handle tool to autofill the below cells.
🔎 How Does the Formula Work?
- COUNTIFS($C$5:C5,C5,$B$5:B5,B5): Excel COUNTIFS function will return the number of cells from the given criteria range. Here this will return the number of C5 that exist between the criteria range1 $C$5:C5 & B5 from the criteria range2 $B$5:B5. We will need to make the first point of the range absolute to make it fixed. That means it won’t change by dragging.
- IF(COUNTIFS($C$5:C5,C5,$B$5:B5,B5)>1,0,1): Excel IF function will return 1 if the name is found for the first time and 0 if it is seen again.
Step 3:
- Then select any cell from the range.
- Go to the Insert tab and select PivotTable from the Tables group.
Step 4:
- A pivot table window pops up. From that, select the table or range.
- Choose where we want to see the pivot table.
- Click on OK.
Step 5:
- A pivot table is almost ready. After that, insert LOCATION in Rows and Count D in Values by dragging.
Step 6:
- Finally, a pivot table is ready and we can see the number of employees in each location.
Related Content: How to Count Duplicates in Column in Excel (3 Ways)
2. Use of Data Model to Count Duplicates in Excel Pivot Table
The data model is the new feature of the pivot table in Excel 2013 and above versions. We can count duplicates by using this feature. We don’t need any helping column here for that. Let’s say, we have a dataset (B4:E10) of Employees with their locations, sold products, and amount of sold products. We are going to find out the total number of employees who work there based on their locations.
Step 1:
- In the beginning, select any cell from the dataset.
- Now go to the Insert tab.
- Select PivotTable from the Tables group.
Step 2:
- From the pivot table window, select the table or range.
- Next, select the place where we want to see the table.
- Make sure to check on the ‘Add this data to the Data Model’ option.
- Click on OK.
Step 3:
- We can see the PivotTables Fields window.
- Insert LOCATION in the Rows area and EMPLOYEE in the Values area.
Step 4:
- Here we can see the summary of the employee numbers in each region.
Step 5:
- After that, select any cell from the Count of EMPLOYEE column and Right-click on it.
- Select the Value Field Settings.
Step 6:
- A Value Field Settings dialogue box pops up.
- Choose the type of calculation as ‘Distinct Count’ from the drop-down.
- Click on OK.
Step 7:
- In the end, we can see the number of employees in each region.
Read More: How to Count Duplicate Values Only Once in Excel
Conclusion
By using these ways, we can easily count duplicates in the Excel pivot table. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.
