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 about the worksheet. Excel provides some basic functions and formulas by which you can easily count duplicate values in a column. Today, in this article, we will learn how to count Duplicates in columns in Excel.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
Count Duplicates in Column (3 Ways)
1. Using the COUNTIF Function to Count Duplicates in Column
The basic COUNTIF function helps you to find duplicates in columns for two different scenarios. Let’s learn!
i. Count Duplicates in a Column Including the First Occurrence
Step-1:
Consider a situation where we are given a dataset containing columns “Name”, “Salary”, and “Region” of some sales rep. We have to find duplicates in the “Region” column.
Step-2:
Create a new dataset with the columns “Region”, and “Count”. In the Region column, the unique names of the regions are given. We will find their duplicates in the Count column.
Step-3:
In cell G4, apply the COUNTIF function. The generic function is,
=COUNTIF(Range, Criteria)
Insert the values into the function and the final form is,
Where,
- The range is $D$4:$D$15.
- The criterion is
Press Enter to get the result.
Step-4:
Now move your mouse cursor to the bottom right corner of the formula cell until you see the fill handle icon (+). Then double click on the icon to get the result for the rest of the cells.
ii. Count Duplicates in a Column Excluding the First Occurrence
Step-1:
In this case, we will ignore the first appearance and the count rest of the duplicates in columns. In cell G4 apply this formula,
Here using this formula we will exclude the first appeared duplicate.
Press Enter to get the result.
Step-2:
So we have got the number of duplicates in columns ignoring the first occurrence. Now do the same for the rest of the cells.
2. Using the SUM with the EXACT Function to Count Case-Sensitive Duplicates in a Column
The COUNTIF function in Excel is case-insensitive. So we will use the combination of the EXACT and the SUM functions for case-sensitive duplicates in columns.
Step-1:
In the given example the column “Salary Grade” contains some case-sensitive duplicates. We have to count those duplicates in columns and find results in the Count column.
Step-2:
Now we will use the SUM with the EXACT formula to count duplicates in columns. Insert the values and the final formula is,
Where,
- Text1 is $D$4:$D$15
- Text2 is G4
To convert the values to an array of 0 and 1’s, the unary operator (–) is used.
Since this formula is an array formula, press “CTRL + SHIFT + ENTER” to apply the formula.
Step-3:
Our case-sensitive duplicate count is here. Now we will get the rest of the duplicate counts.
3. Using the IF with COUNTIF Function to Count Total Duplicates in Columns
Step-1:
In this case, we will count duplicates in the Region column under the Duplicate column and find out the total duplicates in this column.
Step-2:
To do this, apply the If with the COUNTIFS formula. After inputting the values, the final formula is,
Where if the COUNTIF function counts values more than once, the IF function argument will show “DUPLICATE”.
Press Enter to get the result.
Step-3:
Do the same for the rest of the column cells.
Step-4:
To count the total duplicates, apply this formula.
Press Enter to get the total duplicates in columns.
Step-5:
Now if we want to count the duplicates in a column excluding the first occurrence, input this formula
Now press Enter to get the result.
And get the total count by using the same formula. And our job is done.
Things to Remember
⏩Always use the Absolute Cell Reference ($) to Block the range.
⏩ While counting the case-sensitive duplicates, make sure to apply the formula as an Array Formula by Pressing CTRL+SHIFT+ENTER simultaneously.
⏩ Use the unary operator (–) to transform the result of the EXACT function to an array of 0 and 1’s.
Conclusion
Today we discussed how to count duplicates in columns. We hope this article proves useful to you. If you have any ideas or suggestions, you are welcome to share your thoughts in the comment box.
Similar Articles for You to Explore
- How to Count Duplicates in Excel (5 Methods)
- Find Matches or Duplicate Values in Excel (8 Ways)
- How to Count Duplicate Values Only Once in Excel
- VLOOKUP to Find Duplicates in Two Columns
- How to Count Unique Names in Excel (5 Methods)
The post How to Count Duplicates in Column in Excel (3 Ways) appeared first on ExcelDemy.
