In this lesson, I’ll show you a few ways you can use to count duplicate values in Excel. Some of these formulas are more complicated than others.

We are going to use the following example.

Let’s make it more appealing visually go to **HOME >> Styles >> Conditional
Formatting >> Highlight Cells Rules** and select **Duplicate Values.**

Contents

## Count all duplicate values

The first formula is the most complicated one. It’ll count the number o duplicates and sum them, so we will get a single result.

Enter this formula, and press **Ctrl + Shift + Enter** to insert it as an array formula.

`=ROWS($A$2:$A$10)-SUM(IF(COUNTIF($A$2:$A$10,$A$2:$A$10)=1,1,0))`

## Count duplicate instances

Now, instead of counting all duplicates at once, let’s separate them into different names, and count how many times the same name appears.

Enter the following formula into cell **D2** and AutoFill them for other results.

`=COUNTIF($A$2:$A$10,C2)`

## Count duplicate instances without the first occurrence

It’s very similar to the last method. Just subtract 1 if the value is at least one. We don’t want to subtract from values that don’t appear inside the column because we would get negative values.

In this case, let’s use the IF function.

`=IF(COUNTIF($A$2:$A$10,C2)>0,COUNTIF($A$2:$A$10,C2)-1,0)`

The second argument of the **IF** function will be executed if the result of the COUNTIF function is greater than 0, otherwise, it returns 0.

You can count all duplicates by summing the occurrences.

## Count duplicates without using the function

There is a way to count the number of duplicates without using the complicated formula.

Select values you want to find duplicates and navigate to **Data >> Sort & Filter >>
Advanced**. Select **Copy to another
location** and check **Unique records
only**.

After you click **OK**,
you are going to have unique values next to your list. The difference is the
number of duplicates.