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

### 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:** **Excel VBA to Find Duplicate Values in Range (7 Examples)**

### 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 Find Duplicates in Excel Workbook (4 Methods)**

### 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 Find Duplicates in a Column Using Excel VBA (5 Ways)**

**Similar Readings**

**How to Find Matching Values in Two Worksheets in Excel (4 Methods)****Find Similar Text in Two Columns in Excel (3 Easy Ways)****How to Compare Rows for Duplicates in Excel (3 Easy Ways)****Find and Highlight Duplicates in Excel (3 Handy Ways)****How to Find & Remove Duplicate Rows 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:** **Excel Formula to Find Duplicates in One Column**

### 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:** **Find Duplicates in Two Columns in Excel (6 Suitable Approaches)**

## 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:** **How to Use VBA Code to Find Duplicate Rows in Excel (3 Methods)**

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

**Related Articles**

**How to Find Duplicates in Excel and Copy to Another Sheet (5 Methods)****Excel Top 10 List with Duplicates (2 Ways)****How to Highlight Duplicate Rows in Excel (3 Ways)****Excel Find Duplicate Rows Based on Multiple Columns****How to Find Duplicates without Deleting in Excel (7 Methods)****Excel Find Duplicates in Column and Delete Row (4 Quick Ways)****How to Filter Duplicates in Excel (7 Easy Ways)**