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â€ť and other functions. In this article, we will learn how to count duplicates in excel using five different methods.

**Table of Contents**hide

**Download Practice Workbook**

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

**Five Methods to Count Duplicates in Excel**

**1. ****How to Count Duplicate Values Including the First Occurrence**

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

**Step-1:**

The following example includes a dataset where we have the name of different items with their quantity and grades. We have to count duplicates in the **â€śGradeâ€ť** column

**Step-2:**

Create a table anywhere in the worksheet where we will count the duplicates.

**Step-3:**

Now, we will count this by using the **â€śCOUNTIFâ€ť **function.

The format of this function is

**=COUNTIF( Range, Criteria)**

Where,

**Range**: the data range from where we want to count duplicate values**Criteria**: The specific value which will be counted

Apply the **â€śCOUNTIFâ€ť** function.

Here,

- The range is
**$D$4:$D$12.** - The criterion is
**F4.**

Press **â€śEnterâ€ť**.

We have got the duplicate count for** â€śAâ€ť**. Now apply the same function for all the criteria by selecting and dragging your cursor.

**2. ****How to Count Duplicate Values excluding the First Occurrence**

**Step-1:**

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

Apply the **â€śCOUNTIFâ€ť** formula. The final formula is,

**=COUNTIF($D$4:$D$12,F4)-1**

Press **â€śEnterâ€ť**. The duplicate values are shown without the first occurrence.

**Step-2:**

Select the cell and drag the cursor to get all the results.

**3. ****How to Count Case-Sensitive Duplicates in Excel**

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.

**Step-1:**

Consider a table with case-sensitive values like the given one in the picture.

**Step-2:**Apply this formula in the cell where you want to get the duplicate values

Where,

**Text1**is**$D$4:$D$12****Text2**is**F4**

To convert the values to an array of 0 and 1â€™s, the unary operator **(- -)** is used.

Press **â€śCTRL + SHIFT + ENTERâ€ť** to transform the formula as an array formula.

Our case-sensitive duplicate count is here. Now we will get the rest of the duplicate counts.

**Similar Readings:**

**4. ****Counting Duplicate Rows in Excel**

This method can be done in two ways. Letâ€™s discuss.

**i. Including the First Occurrence**

**Step-1:**

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.

**Step-2:**

To count the duplicate values we will use the **â€śCOUNTIFSâ€ť** function.

The final form is the function is

**=COUNTIFS($B$4:$B$12,B4,$C$4:$C$12,C4,$D$4:$D$12,D4)**

Get the result by pressing **â€śEnterâ€ť**.

**Step-3:**

Do the same for the rest of the duplicate rows.

**ii.** **Excluding the First Occurrence**

**Step-1:**

To get the duplicate rows without the first occurrence, apply this formula

**=COUNTIFS($B$4:$B$12,B4,$C$4:$C$12,C4,$D$4:$D$12,D4)-1**

Press **â€śEnterâ€ť** to get the value.

**Step-2:**

Apply the same formula by clicking and dragging the mouse.

**5. ****How to Count the Total Number of Duplicates in a Column**

Like counting duplicate rows, we can count duplicates in a column in excel. Letâ€™s see how.

**i. Including the First Occurrence**

**Step-1:**

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

**Step-2:**

Apply the** â€śIFâ€ť** and the **â€śCOUNTIFâ€ť** function.

The final form of the formula is

**=IF(COUNTIF($B$4:$B$4:$B$12,B4)>1,â€ťDUPLICATEâ€ť,â€ťâ€ť)**

Press **â€śEnterâ€ť** and drag the formula to the other cells to get the duplicate values in a column.

**Step-3:**

To count the total duplicates, apply this formula.

Press **â€śEnterâ€ť**. We got the values of the total duplicates.

**ii. Excluding the First Occurrence**

**Step-1:**

To count duplicates in a column excluding the first occurrence, apply this formula

**=IF(COUNTIF($B$4:B4,B4)>1,â€ťYESâ€ť,â€ťâ€ť)**

Where we changed the cell references from **$B$4:$B$4:$B$12** to **$B$4:B4**

Press** â€śEnterâ€ť**.

So now we got only the duplicate values excluding the first occurrence.

**Step-2:**

Now we will count the duplicates.

Press** â€śEnterâ€ť**. The result is given in the cell.

**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â€ť **simultaneously.

âŹ© Use the unary operator **(- -)** to transform the result of the **â€śEXACTâ€ť** function to an array of 0 and 1â€™s.

**Conclusion**

Today we discussed how to count duplicates in five different conditions. Hope this article is useful to you. Share your thoughts in the comment box.