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.

Example of countif formula which will be used to find duplicates


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.

Dataset for Finding Duplicates Using COUNTIF Formula


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.

Counting Duplicates in a Range with First Occurrence

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

Read More: How to Ignore Blanks and Count Duplicates in Excel


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.

use of COUNTIF formula to find Duplicates Value without First Occurrence

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

Read More: How to Count Duplicate Values Only Once in Excel


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.

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

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.

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.

Read More: How to Count Duplicates with Pivot Table in Excel


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.

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

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

Read More: VBA to Count Duplicates in Range in Excel


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.

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.

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

Read More: Excel VBA to Count Duplicates in a Column


Practice Section

You can practice the explained methods in the download document.


Download the Practice Worksheet


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

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo