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

## An Example of the 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 the COUNTIF Formula to Find Duplicates: 5 Easy Ways

We’ll use the following dataset which has 3 columns: Name, Dept., and Salary. We’ll find the duplicated values across the columns.

### Method 1 – Using the COUNTIF Function to Find Duplicates in a Range Counting First Occurrence

• Make a smaller table to the side to put values that you want to search for (see screenshot below).
• Click on the G7 cell to select it.
• Use this formula in this cell:
`=COUNTIF(\$C\$5:\$C\$14,F7)`

The formula counts the number of values equal to the value in F7 in the data range \$C\$5:\$C\$14.

• Press Enter to get the result.

• Go to the bottom-right corner of cell G7, and the icon will change to a plus called the Fill Handle icon. Click the Fill Handle icon, hold it, and drag until you reach cell G10.
• Release the mouse button.

• You will get the following result.

### Method 2 – Counting Duplicate Values without the First Occurrence

• Click the G7 cell to select it.
• Use this formula:
`=COUNTIF(\$C\$5:\$C\$14,F7)-1`

This is effectively the same formula as before but subtracts 1. If the value isn’t found, it’ll yield -1.

• Press EnterÂ to get the result.

• Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells G8:G10.Â

### Method 3 – Use COUNTIF and IF Functions to Find Duplicates in a Column

• Make a new column E to display the results.
• Click the E5 cell to select it.
• Use this formula:
`=IF(COUNTIF(\$B\$5:B5,B5)>1,"Duplicate","Unique")`
• Press Enter to get the result.

Formula Breakdown

• The COUNTIF function will count those cells whose values fulfill the criteria.
• For the first cell, COUNTIF(\$B\$5:B5,B5)Â becomes 1.
• Here, the IF function will check the given logical test.
• COUNTIF(\$B\$5:B5,B5)>1 denotes the logical test. This test will check whether the count number is greater than 1.
• When the number is greater than 1 then it will return Duplicate.
• If the logic test fails the formula will return Unique.
• So, IF(1>1,”Duplicate”,”Unique”)Â returns Unique.

• Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E14.

• Enter the formula given below in the E16 cell to find the total duplicates.
`=COUNTIF(E5:E14,"Duplicate")`

Read More: How to Count Repeated Words in Excel

### Method 4 – Finding Total Duplicates in a Column Including First Occurrence

Steps:

• Select a new cell D16, where you want to keep the result.
• Use the following formula:
`=ROWS(\$B\$5:\$B\$14)-SUM(IF(COUNTIF(\$B\$5:\$B\$14,\$B\$5:\$B\$14) =1,1,0))`
• Press Enter.

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.

### Method 5 – Applying AND and COUNTIF Functions to Find Duplicate Values within Multiple Columns

We’ll count the duplicates between the January and February columns.

• Select a new cell, E5, where you want to keep the result.
• Use the formula given below:
`=AND(COUNTIF(\$C\$5:\$C\$14,C5),COUNTIF(\$D\$5:\$D\$14,C5))`
• 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

• Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E14.

• Use the formula given below in the E16 cell to find the total duplicates.
`=COUNTIF(E5:E14,TRUE)`
• Press Enter to get the result. You will get the total duplicate number.

## Use the COUNTIFS Function to Find Duplicate Rows in Excel

Steps:

• Select cell E5 and insert the following formula:
`=IF(COUNTIFS(\$B\$5:\$B\$14,\$B5,\$C\$5:\$C\$14,\$C5,\$D\$5:\$D\$14,\$D5)>1, "Duplicate", "")`
• Press Enter.

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.

• Double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E14.

• Use the formula given below in the E16 cell to find the total number of duplicates.
`=COUNTIF(E5:E14,"Duplicate")`
• Press Enter to get the result.

## Practice Section

<< Go Back to Count Duplicates in ExcelÂ | Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF