Say you have a column with duplicate values. Now, you just want to find out which values are duplicate and how many times they are duplicated. In addition, you can find out the duplicate ones with the COUNTIF formula. So, I’m going to show how to use the COUNTIF formula and how to find duplicates using this formula.
An Example of COUNTIF Formula:
COUNTIF(B5:E9, “>250”): Count the value if it is greater than 250 in the cell range B5 to E9.
Now, observe the following image. Here, I have applied the following formula in cell E11.
=COUNTIF(B5:E9, ">250")Here, E11 displays the total numbers that are greater than 250 in the cell range B5 to E9.
How to Use COUNTIF Formula to Find Duplicates: 5 Easy Ways
Here, I will demonstrate five suitable methods to find Duplicates using the COUNTIF function. Furthermore, for your better understanding, I’m going to use the following dataset which has 3 columns. Those are Name, Dept., and Salary.
1. Using COUNTIF Function to Find Duplicates in a Range with First Occurrence
You can find the duplicate values using the COUNTIF function in a range with the first occurrence.
- Firstly, click the G7 cell to select it.
- Secondly, write this formula in this cell:
=COUNTIF($C$5:$C$14,F7)$C$5:$C$14 means the data range and criteria F7 means: the value of cell F7. In one sentence the whole command is: Count in the value if the value is equal to F7 in the data range $C$5:$C$14.
- Thirdly, press ENTER to get the result.
- Now, I will copy the same formula using relative cell references. To do this, select cell G7. A square box will be shown in the bottom-right corner of cell G7, it is called the Fill Handle icon. Click the Fill Handle icon, hold it, and drag until you reach cell G10.
- Then, release the mouse button.
Finally, you will get the following image.
Read More: How to Ignore Blanks and Count Duplicates in Excel
2. Counting Duplicates Value without First Occurrence
You can find the duplicate values using the COUNTIF function in a range excluding the first occurrence.
- Firstly, click the G7 cell to select it.
- Secondly, write this formula in this cell:
=COUNTIF($C$5:$C$14,F7)-1$C$5:$C$14 means the data range and criteria F7 means: the value of cell F7. In one sentence the whole command is: Count in the value if the value is equal to F7 in the data range $C$5:$C$14 and then subtract 1 from the result.
- Thirdly, press ENTER to get the result.
- Now, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells G8:G10.
Finally, you will get the following numbers of duplicates except for 1st occurrence.
Read More: How to Count Duplicate Values Only Once in Excel
3. Use of COUNTIF & IF Functions to Find Total Duplicates Number in a Column
You can find the total duplicate number employing the IF, and COUNTIF functions in a column excluding the first occurrence.
- Firstly, click the E5 cell to select it.
- Secondly, write this formula in this cell:
=IF(COUNTIF($B$5:B5,B5)>1,"Duplicate","Unique")- Thirdly, press ENTER to get the result.
Formula Breakdown
- $B$5:B5 means the data range. And B5 is the criteria.
- Here, the COUNTIF function will count those cells whose values fulfill the criteria.
- COUNTIF($B$5:B5,B5)—> becomes 1.
- Here, the IF function will check the given logical test.
- Firstly, COUNTIF($B$5:B5,B5)>1 denotes the logical test. This test will check whether the count number is greater than 1 or not.
- Secondly, “Duplicate” —> when the number will be greater than 1 then it will return Duplicate. Here, Inverted Comma is a must for getting a text as the output.
- Lastly, “Unique” —> denotes that when the logic fails then it will return Unique.
- So, IF(1>1,”Duplicate”,”Unique”)—> turns Unique.
- Now, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E14.
- Now, you should use the formula given below in the E16 cell to find the total duplicates.
=COUNTIF(E5:E14,"Duplicate")E5:E14 means the data range and criteria “Duplicate”. In one sentence the whole command is: Count in the value if the value is equal to Duplicate in the data range E5:E14.
- Then, press ENTER to get the result.
Lastly, you will see the total duplicate number.
Read More: How to Count Repeated Words in Excel
4. Finding Total Duplicates Number in a Column Including 1st Occurrence
You can employ a combination of some functions like the IF function, COUNTIF function, SUM function, and ROWS function to know the total duplicates in a column including the first occurrence. Furthermore, you can modify these functions for different types of results according to your preference. The steps are given below.
Steps:
- Firstly, you have to select a new cell D16 where you want to keep the result.
- Secondly, you should use the formula given below in the D16 cell.
=ROWS($B$5:$B$14)-SUM(IF(COUNTIF($B$5:$B$14,$B$5:$B$14) =1,1,0))- Finally, press ENTER to get the result.
Formula Breakdown
- COUNTIF($B$5:$B$14,$B$5:$B$14)—> becomes {3,3,2,2,2,2,1,3,2,2}
- IF(COUNTIF($B$5:$B$14,$B$5:$B$14) =1,1,0)—> turns {0,0,0,0,0,0,1,0,0,0}
- SUM(0,0,0,0,0,0,1,0,0,0)—> gives 1.
- ROWS($B$5:$B$14)—> returns 10.
- Output: 10-1 = 9.
Read More: How to Count Duplicates with Pivot Table in Excel
5. Applying AND & COUNTIF Functions to Find Duplicates Value within Multiple Columns
Here, I will use the AND function and COUNTIF function to count the duplicates between the January and February columns.
- Firstly, you have to select a new cell E5 where you want to keep the result.
- Secondly, you should use the formula given below in the E5 cell.
=AND(COUNTIF($C$5:$C$14,C5),COUNTIF($D$5:$D$14,C5))- Finally, press ENTER to get the result.
Formula Breakdown
- Here, $C$5:$C$14 is the range of the January column and $D$5:$D$14 is the range of the February column.
- COUNTIF($C$5:$C$14,C5)—> returns the number of the value in cell C5 in the range $C$5:$C$14.
- Output —> 1
- COUNTIF($D$5:$D$14,C5) —> returns the number of the value in cell C5 in the range $D$5:$D$14
- Output—> 0
- AND(COUNTIF($C$5:$C$14,C5),COUNTIF($D$5:$D$14,C5)) —> becomes AND(1,0)
- Output—> FALSE
- Now, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E14.
Lastly, you will see all the results. When the value duplicates then the Status becomes TRUE.
- Now, you should use the formula given below in the E16 cell to find the total duplicates.
=COUNTIF(E5:E14,TRUE)E5:E14 means the data range and criteria TRUE. In one sentence the whole command is: Count in the value if the value is equal to TRUE in the data range E5:E14.
- Then, press ENTER to get the result.
Lastly, you will see the total duplicate number.
Read More: VBA to Count Duplicates in Range in Excel
Use of COUNTIFS Function to Find Duplicate Rows in Excel
You can use the COUNTIFS function to find duplicate row numbers. The steps are given below.
Steps:
- Firstly, you have to select a new cell E5 where you want to keep the result.
- Secondly, you should use the formula given below in the E5 cell.
=IF(COUNTIFS($B$5:$B$14,$B5,$C$5:$C$14,$C5,$D$5:$D$14,$D5)>1, "Duplicate", "")- Finally, press ENTER to get the result.
Formula Breakdown
- $B$5:$B$14 is the data range 1 and $B5 is the criteria 1.
- $C$5:$C$14 is the data range 2 and $C5 is the criteria 2.
- $D$5:$D$14 is the data range 3 and $D5 is the criteria 3.
- COUNTIFS($B$5:$B$14,$B5,$C$5:$C$14,$C5,$D$5:$D$14,$D5)—> becomes 2.
- IF(2>1, “Duplicate”, “”)—> gives Duplicate.
- Now, you can double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E14.
Lastly, you will see all the results. When the value duplicates then the Status becomes Duplicate.
- Now, you should use the formula given below in the E16 cell to find the total duplicates.
=COUNTIF(E5:E14,"Duplicate")E5:E14 means the data range and criteria “Duplicate”. In one sentence the whole command is: Count in the value if the value is equal to “Duplicate” in the data range E5:E14.
- Then, press ENTER to get the result.
Lastly, you will see the total number of duplicate rows.
Read More: Excel VBA to Count Duplicates in a Column
💬 Things to Remember
- You have to select the methods as per your requirement and your dataset.
Practice Section
Now, you can practice the explained method by yourself.
Download Working File
Download the working file from the link below:
Conclusion
I hope you found this article helpful. Here, I have explained 5 suitable methods to find the number of Duplicates using the COUNTIF function. Please, drop comments, suggestions, or queries if you have any in the comment section below.
<< Go Back to Count Duplicates in Excel | Duplicates in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
