While working with a large amount of data or compiling multiple worksheets into a bigger one, duplicate values, columns or rows may appear. Sometimes we need to count them to get a clear idea. You can do this easily by using the** COUNTIF function** and other functions. In this article, we will learn how to **count duplicates in excel** using six different methods.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice sheet to practice while you are reading this article.

**6 Methods to Count Duplicates in Excel**

The following dataset has the** Items**, **Qty**, and** Grade** Columns. Here, you can easily notice that the dataset has **duplicate values**. Furthermore, using this dataset, we will **count duplicates in Excel**. Here, we used **Excel 365**. You can use any available Excel version.

**1. Counting Duplicates in Excel Including First Occurrence**

In this method, we will learn to **count duplicate values** including the first occurrences.

Here, the following dataset has the name of different** Items** with their **Qty** and **Grade**. We have to count duplicates in the **Grade** column.

**Steps:**

- First of all, we will type the following formula in cell
**G5**.

`=COUNTIF($D$5:$D$13,F5)`

**Formula Breakdown**

**COUNTIF($D$5:$D$13,F5) → the COUNTIF function**counts the number of cells that meet the given criteria.**$D$5:$D$13 →**is the**range**.**F5****→**is the**criteria**.**COUNTIF($D$5:$D$13,F5) →**becomes**Output: 3**

**Explanation:**here,**3**indicates the number of times grades**A**was found.

- After that, press
**ENTER**.

Hence, you can see the result in cell** G5**.

- Furthermore, we will drag down the formula with the
**Fill Handle tool**.

As a result, you can see the **Grades** with their duplicate count.

**Read More: ****Count the Order of Occurrence of Duplicates in Excel (4 Methods)**

**2. Calculating Number of Duplicates Excluding First Occurrence**

Here we will **count duplicate** values without considering the first occurrence.

**Steps:**

- In the beginning, we will type the following formula in cell
**G5**.

`=COUNTIF($D$5:$D$13,F5)-1`

Here, **COUNTIF($D$5:$D$13,F5)-1** subtracts 1 from the result returned from the **COUNTIF** function. As a result, the overall number of duplicates becomes **1** less than the total number of duplicates. Thus, the first occurrence becomes excluded from the total number of duplicates.

- After that, press
**ENTER**.

Therefore, you can see the result in cell **G5**.

- Moreover, we will drag down the formula with the
**Fill Handle tool**.

Therefore, you can see the number of duplicates of** Grades** excluding the first occurrence.

**Read More:**** Count Number of Occurrences of Each Value in a Column in Excel**

**3. Finding Number of Case-Sensitive Duplicates in Excel**

In the following dataset, you can see that in the **Grade **column, we have case-sensitive duplicates. The** COUNTIF** function in Excel is case-insensitive. So we need to apply different functions to get the case-sensitive duplicates. We will use the combination of the **EXACT** and **SUM** functions in this case.

**Steps:**

- First of all, we will type the following formula in cell
**G5**.

`=SUM(--EXACT($D$5:$D$13,F5))`

**Formula Breakdown**

**EXACT($D$5:$D$13,F5) →**the**EXACT**function compares 2 text strings and returns True if they are exactly similar.**Output:**here, the**Exact**function returns 2**TRUE**for Grade**A**since Grade**A**has 2 duplicates.

**SUM(–EXACT($D$5:$D$13,F5)) →**the**SUM**function sums up the**2**Trues and gives a numerical value.**Output: 2**

**Explanation:**Here,**2**indicates the number of duplicate counts for Grade**A**.

- If you are using
**Excel 365**, press**ENTER**. As a result, you can see the result in cell**G5**.

**Note:** Since this is an array formula, press **CTRL+SHIFT+ENTER **if you do not have **Excel 365**.

- Moreover, we will drag down the formula with a
**Fill Handle tool**.

Hence, you can see the case-sensitive duplicate counts.

**Read More:** **How to Count Duplicates Based on Multiple Criteria in Excel**

**4. Counting Duplicate Rows in Excel**

Here, we will **count duplicate rows in Excel**. This method can be done in two ways. Let’s discuss this.

**4.1. Including First Occurrence**

In the following example, we have some duplicate rows containing identical information. We will now learn how to count these rows including the first occurrence.

**Steps:**

- First of all, we will type the following formula in cell
**G5**.

`=COUNTIFS($B$5:$B$13,B5,$C$5:$C$13,C5,$D$5:$D$13,D5)`

**Formula Breakdown**

**COUNTIFS($B$5:$B$13,B5,$C$5:$C$13,C5,$D$5:$D$13,D5)—->****the COUNTIFS function**applies criteria to a range of cells and counts the number of times the criteria met.**Output: 3**

**Explanation:**here,**3**indicates the number of times the item**Apple**is found.

- Afterward, press
**ENTER**.

Then, you will see the result in cell **G5**.

Moreover, we will drag down the formula with the **Fill Handle tool**.

** **

Therefore, you can see the **duplicate row** count.

**4.2. Excluding First Occurrence**

Here, we will find out the duplicate rows excluding the first occurrence.

**Steps:**

In the beginning, we will type the following formula in cell **G5**.

`=COUNTIF($D$5:$D$13,F5)-1`

Here, **COUNTIF($D$5:$D$13,F5)-1 **subtracts 1 from the result returned from the **COUNTIF** function. As a result, the overall number of duplicates becomes **1** less than the total number of duplicates. Thus, the first occurrence becomes excluded from the total number of duplicates.

**ENTER**.

Then, you will see the result in cell **G5**.

- Moreover, we will drag down the formula with the
**Fill Handle tool**.

Hence, you can see the result in cells **G5:G8**.

**Read More:** **How to Count Occurrences Per Day in Excel (4 Quick Ways)**

**5. Total Duplicate Values in a Column**

Like counting duplicate rows, we can **count duplicates in a column in excel**. Let’s see how.

**5.1. Including First Occurrence**

Consider a table where we have duplicate values in a column. We have to count those duplicate values.

**Steps:**

First of all, to find out the duplicate Items, we will type the following formula in cell **C5**.

`=IF(COUNTIF($B$5:$B$5:$B$13,B5)>1,"DUPLICATE","")`

**Formula Breakdown**

**COUNTIF($B$5:$B$5:$B$13,B5) → the COUNTIF function**counts the number of cells that meet the given criteria.**$B$5:$B$5:$B$13 →**is the**range**.**B5****→**is the**criteria**.**COUNTIF($B$5:$B$5:$B$13,B5) →**becomes**Output: 1**

**IF(COUNTIF($B$5:$B$5:$B$13,B5)>1,”DUPLICATE”,””) →****the IF function**makes a logical comparison between a value and the value we expect.**Output:**Blank cell.

**Explanation:**Since the logical comparison of the**IF**function is**FALSE**, it returns a Blank cell.

- After that, press
**ENTER**.

Therefore, you can see that cell **C5** is a blank cell.

- Furthermore, we will drag down the formula with a
**Fill Handle tool**to cell**C13**.

Therefore, you can see the** DUPLICATE** in the **Duplicate** column.

Next, we will find out the Duplicates in the column.

- After that, we will type the following formula in cell
**C14**.

`=COUNTIF($C$5:$C$13,"DUPLICATE")`

- Moreover, press
**ENTER**.

Therefore, you can see the duplicate count in a column in cell **C14**.

**5.2. Excluding First Occurrence**

Here, we will** count duplicates** in a column excluding the first occurrence.

**Steps:**

- First of all, we will type the following formula in cell
**C5**.

`=IF(COUNTIF($B$5:B5,B5)>1,"YES","")`

- After that, press
**ENTER**.

Therefore, you can see that cell **C5** is a blank cell.

- Furthermore, we will drag down the formula with a
**Fill Handle tool**to cell**C13**.

Then, you can see duplicate **YES** in the **Duplicate** column.

In addition, we will calculate the duplicate in the column excluding the first occurrence.

- Moreover, we will type the following formula in cell
**C14**.

`=COUNTIF(C5:C13,"YES")-1`

Here, **COUNTIF(C5:C13,”YES”)-1 **subtracts 1 from the result returned from the **COUNTIF** function. As a result, the overall number of duplicates becomes **1** less than the total number of duplicates. Thus, the first occurrence becomes excluded from the total number of duplicates.

- Afterward, press
**ENTER**.

Then, you can see the number of duplicates in a column excluding the first occurrence in cell **C14**.

**Read More:** **How to Count Duplicates in Two Columns in Excel (8 Methods)**

### 6. Inserting Pivot Table in Excel to Count Duplicates

In this method, we will use a **Pivot Table** to** count duplicates in Excel**. This method is a handy and easy way to do the task.

**Steps:**

- In the beginning, we will select the dataset by selecting cells
**B4:D13**. - After that, go to the
**Insert**tab. - Then, from the
**PivotTable**group >> we will select From**Table/Range**.

At this point, a **PivotTable from table or range** dialog box will appear.

- Then, we will select the
**Existing Worksheet**. - Moreover, we will select cell
**F4**in the**Location**box. - Furthermore, click
**OK**.

Then, a **PivotTable Fields **dialog box will appear at the right end of the Excel sheet.

- Afterward, we will drag
**Grade**in the**Rows**and**Values**group.

Therefore, you can see the** duplicate count** of the **Grade** in the **Pivot Table**.

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

**Things to Remember**

- Always use the
**“Absolute Cell Reference ($)”**to**“Block”**the range - While counting the case-sensitive duplicates, make sure to apply the formula as an
**“Array Formula”**by Pressing**“CTRL+SHIFT+ENTER”** - Use the unary operator
**(- -)**to transform the result of the**“EXACT”**function to an array of**0**and**1’s**.

## Practice Section

You can download the above** Excel **file to practice the explained method.

## Count Duplicates in Excel: Knowledge Hub

**Count Repeated Words****Count Duplicate Values Only Once****Ignore Blanks and Count Duplicates****Count Duplicates Based on Multiple Criteria****Count Duplicate Rows****Count Duplicates in Column****VBA to Count Duplicates in a Column****Count Duplicates in Two Columns****Count Duplicate Values in Multiple Columns****VBA to Count Duplicates in Range****Count the Order of Occurrence of Duplicates****Count Number of Occurrences of Each Value in a Column****Count Occurrences Per Day**

## Conclusion

Here, we tried to show you** 6** methods to** count duplicates in Excel.** Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website **Exceldemy** to explore more.