How to Use COUNTIF Formula to Find Duplicates (5 Easy Ways)

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.


Download Working File

Download the working file from the link below:


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.

Example of countif formula which will be used to find duplicates


5 Methods to Find Duplicates Using COUNTIF Formula in Excel

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.

Dataset for Finding Duplicates Using COUNTIF Formula

Read more: How to Highlight Duplicate Rows in Excel


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.

Counting Duplicates in a Range with First Occurrence

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


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.

use of COUNTIF formula to find Duplicates Value without First Occurrence

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


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.

Use of COUNTIF & IF Functions to Find Total Duplicates Number in a Column

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.


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.

Finding Total Duplicates Number in a Column Including 1st Occurrence

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.


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.

Counting Duplicates Value within Multiple Columns

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.


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.

Use of COUNTIFS Function to Find Duplicates Rows

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.


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


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. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Read More

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo