Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


4 Suitable Ways to Count Duplicates in Column in Excel

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.

excel count duplicates in column Sample Dataset


1. Insert 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 Compute 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)

Insert COUNTIF Function in Excel to Count Duplicates in Column Including First Occurrence

  • At last, hit the Enter key to count the number of duplicates.


1.2 Count 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

Insert COUNTIF Function in Excel to Count Duplicates in Column Excluding First Occurrence

  • In the end, press the Enter button to count the number of duplicates.

Read More: VBA to Count Duplicates in Range in Excel (4 Methods)


2. Combine 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.

excel count duplicates in column

๐Ÿ“Œ Steps:

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

=SUM(--EXACT($D$5:$D$11,D5))

Combine SUM and EXACT Functions for Counting Case-Sensitive Duplicates in Columns

๐Ÿ”Ž 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.

Read More: Count Number of Occurrences of Each Value in a Column in Excel


3. Find Total Duplicates in Column by Joining IF and COUNTIFS

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.

excel count duplicates in column

๐Ÿ“Œ Steps:

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

=IF(COUNTIFS($C$4:$C$11,C5)>1,"DUPLICATE","")

Find Total Duplicates in Column by Joining IF and COUNTIFS in Excel

๐Ÿ”Ž 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. Count 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.

Count Duplicates in Column with Excel Pivot Table

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

Read More: How to Count Duplicates Based on Multiple Criteria in Excel


Similar Readings


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.

Read More: Excel VBA to Count Duplicates in a Column (A Complete Analysis)


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.

For more information like this, visit Exceldemy.com.


Related Articles

 

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo