While working with a large dataset or merging multiple worksheets into one, there is a possibility that you are getting duplicate values or columns into the worksheet. Sometimes we may need to count those duplicate values to get a clear concept about the worksheet. Excel provides some basic functions and formulas by which you can easily count duplicate values in a column. Today, in this article, we will learn how to count duplicates in columns in Excel.

## Download Practice Workbook

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

## Count Duplicates in Column (3 Ways)

### 1. Using the COUNTIF Function to Count Duplicates in Column

The basic **COUNTIF** function helps you to find duplicates in columns for two different scenarios. Let’s learn!

#### i. Count Duplicates in a Column Including the First Occurrence

**Step-1:**

Consider a situation where we are given a dataset containing columns **“Name”, “Salary”,** and **“Region”** of some sales rep. We have to find duplicates in the **“Region”** column.

**Step-2:**

Create a new dataset with the columns **“Region”, **and **“Count”.** In the Region column, the unique names of the regions are given. We will find their duplicates in the Count column.

**Step-3:**

In cell **G4**, apply the **COUNTIF** function. The generic function is,

**=COUNTIF( Range, Criteria)**

Insert the values into the function and the final form is,

**=COUNTIF($D$4:$D$15,F4)**

Where,

- The range is
**$D$4:$D$15.** - The criterion is

Press **Enter **to get the result.

**Step-4:**

Now move your mouse cursor to the bottom right corner of the formula cell until you see the fill handle icon (**+**). Then double click on the icon to get the result for the rest of the cells.

#### ii. Count Duplicates in a Column Excluding the First Occurrence

**Step-1:**

In this case, we will ignore the first appearance and the count rest of the duplicates in columns. In cell **G4** apply this formula,

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

Here using this formula we will exclude the first appeared duplicate.

Press** Enter **to get the result.

**Step-2:**

So we have got the number of duplicates in columns ignoring the first occurrence. Now do the same for the rest of the cells.

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

### 2. Using the SUM with the EXACT Function to Count Case-Sensitive Duplicates in a Column

The** COUNTIF** function in Excel is case-insensitive. So we will use the combination of the **EXACT** and the **SUM **functions for case-sensitive duplicates in columns.

**Step-1:**

In the given example the column **“Salary Grade”** contains some case-sensitive duplicates. We have to count those duplicates in columns and find results in the Count column.

**Step-2:**

Now we will use the **SUM** with the **EXACT **formula to count duplicates in columns. Insert the values and the final formula is,

**=SUM(–EXACT($D$4:$D$15,G4))**

Where,

**Text1**is**$D$4:$D$15****Text2**is**G4**

To convert the values to an array of 0 and 1’s, the unary operator **(–)** is used.

Since this formula is an array formula, press **“CTRL + SHIFT + ENTER”** to apply the formula.

**Step-3:**

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

### 3. Using the IF with COUNTIF Function to Count Total Duplicates in Columns

**Step-1:**

In this case, we will count duplicates in the** Region** column under the **Duplicate **column and find out the total duplicates in this column.

**Step-2:**

To do this, apply the If with the **COUNTIFS** formula. After inputting the values, the final formula is,

**=IF(COUNTIF($D$4:$D$15,D4)>1,”DUPLICATE”,””)**

Where if the **COUNTIF** function counts values more than once, the **IF **function argument will show “**DUPLICATE**”.

Press **Enter **to get the result.

**Step-3:**

Do the same for the rest of the column cells.

**Step-4:**

To count the total duplicates, apply this formula.

**=COUNTIF(E4:E15,E4)**

Press **Enter **to get the total duplicates in columns.

**Step-5:**

Now if we want to count the duplicates in a column excluding the first occurrence, input this formula

**=IF(COUNTIF($D$4:D4,D4)>1,”YES”,””)**

Now press **Enter **to get the result.

And get the total count by using the same formula. And our job is done.

## 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 columns. We hope this article proves useful to you. If you have any ideas or suggestions, you are welcome to share your thoughts in the comment box.