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

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