# How to Count Duplicates in Column in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working with a large dataset or merging multiple worksheets into one, there is a possibility that you are getting duplicate values or columns into the worksheet. Sometimes, we may need to count those duplicate values to get a clear concept of the worksheet. However, Excel provides some basic functions and formulas by which you can easily count duplicate values in a column. In this article, I will show you 4 suitable ways to count duplicates in a column in Excel. Hence, read through the article to learn more and save time.

In this tutorial, I will show you how to count duplicates in columns in Excel. Here, I have used 4 different methods to make you understand the scenario properly. For the purpose of demonstration, I have used the following sample dataset. Here, the dataset includes the Name of the employee and Region. ## 1. Inserting COUNTIF Function in Excel to Count Duplicates in Column

Fortunately, the basic COUNTIF function helps you to find duplicates in columns for two different scenarios. For instance, we will show you two examples of counting duplicates in columns including the first occurrence and excluding the first occurrence. Let’s learn!

### 1.1 Counting Duplicates Including First Occurrence

Consider a situation where we are given a dataset containing columns Name, and Region of some sales employees. Here, we have to find duplicates in the Region column and we will include the first occurrence. Hence, follow the steps below.

📌 Steps:

• First of all, select cell C13 and write down the following formula.

`=COUNTIF(C5:C11,C6)` • At last, hit the Enter key to count the number of duplicates. ### 1.2 Counting Duplicates Excluding First Occurrence

In this case, we will ignore the first appearance and the count rest of the duplicates in columns. However, the process is similar to the previous one.

📌 Steps:

• In the beginning, select cell C13 and enter the following formula.

`=COUNTIF(\$C\$5:\$C\$11,C6)-1` • In the end, press the Enter button to count the number of duplicates. ## 2. Combining SUM and EXACT Functions for Counting Case-Sensitive Duplicates

Unfortunately, the COUNTIF function in Excel is case-insensitive. Here, we will use the combination of the EXACT and the SUM functions for case-sensitive duplicates in columns. So, read through the steps mentioned below. For the purpose of the demonstration, we have changed the dataset a little bit. 📌 Steps:

• Initially, click on cell D13 and enter the formula below.

`=SUM(--EXACT(\$D\$5:\$D\$11,D5))` 🔎 Formula Breakdown:

• In the Exact function, Text1 is \$D\$5:\$D\$11 and the Text2 is D5(A). Usually, it searches Text2 from the range Text1.
• However, to convert the values to an array of 0 and 1’s, the unary operator () is used.
• Finally, the SUM function provides the sum of the exact match of cell D5.
• Lastly, press the Enter button in order to get the final output. ## 3. Finding Total Duplicates in Column by Joining IF and COUNTIFS in Excel

Furthermore, we will combine the IF and COUNTIFS functions to count total duplicates in a column in Excel. Hence, follow the steps below in order to complete the operation properly. For instance, I have slightly modified the sample dataset. 📌 Steps:

• Firstly, click on cell D5 and enter the formula below.

`=IF(COUNTIFS(\$C\$4:\$C\$11,C5)>1,"DUPLICATE","")` 🔎 Formula Breakdown:

• Initially, the COUNTIFS function counts the text in cell C5 from the range \$D\$5:\$D\$11.
• Then, the IF function checks the output with the given condition.
• Finally, if the COUNTIFS function counts values more than once, the IF function argument will show “DUPLICATE” otherwise blank.
• Secondly, press Enter and apply the AutoFill tool to the entire column. • After that, select cell D13 and insert the formula below.

`=COUNTIF(D5:D11,D6)` • Finally, hit Enter to find the total count of duplicates. ## 4. Counting Duplicates in Column with Excel Pivot Table

Last but not least, we will use a Pivot Table to count duplicates in columns in Excel. However, this method is a handy and easy way to do the task. Hence, go through the steps mentioned below.

📌 Steps:

• Firstly, we will select the dataset by selecting cells B4:C11.
• Secondly, go to the Insert tab and from the PivotTable group, we will select From Table/Range. • At this point, a PivotTable from table or range dialog box will appear.
• Then, check the Existing Worksheet and select cell F4 in the Location box.
• Furthermore, click OK. • Then, a PivotTable Fields dialog box will appear at the right end of the Excel sheet.
• Afterwards, drag Region in the Rows and Values group. • Finally, you can see the duplicate count of the Region in the Pivot Table. ## Things to Remember

• First, use the Absolute Cell Reference (\$) to Block the range always.
• Second, while counting the case-sensitive duplicates, make sure to apply the formula as an Array Formula by Pressing CTRL+SHIFT+ENTER simultaneously.
• Last, use the unary operator () to transform the result of the EXACT function to an array of 0 and 1’s.

## Conclusion

These are all the steps you can follow to count duplicates in columns in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

## Related Articles Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  