Count Duplicates in Excel Pivot Table (2 Easy Ways)

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.

Insert a Helping Column to Count Duplicates in Excel Pivot Table

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)

Insert a Helping Column to Count Duplicates in Excel Pivot Table

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.

Insert a Helping Column to Count Duplicates in Excel Pivot Table

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.

Counting Duplicates by Data Model in Excel Pivot Table

Step 1:

  • In the beginning, select any cell from the dataset.
  • Now go to the Insert tab.
  • Select PivotTable from the Tables group.

Counting Duplicates by Data Model in Excel Pivot Table

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.

Counting Duplicates by Data Model in Excel Pivot Table

Step 3:

  • We can see the PivotTables Fields window.
  • Insert LOCATION in the Rows area and EMPLOYEE in the Values area.

Counting Duplicates by Data Model in Excel Pivot Table

Step 4:

  • Here we can see the summary of the employee numbers in each region.

Counting Duplicates by Data Model in Excel Pivot Table

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.


Related Articles

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo