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

**Table of Contents**hide

## 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**.

## 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**.

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

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

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

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

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

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

**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**

**How to Find, Highlight & Remove Duplicates in Excel****How to Find Matching Values in Two Worksheets in Excel (4 Methods)****Find Duplicates in Two Columns in Excel (6 Suitable Approaches)****How to Compare Rows in Excel for Duplicates****Excel Find Similar Text in Two Columns****Excel Top 10 List with Duplicates****Find Matches or Duplicate Values in Excel (8 Ways)****Excel Formula to Find Duplicates in One Column**