In Excel, you can identify the occurrence of duplicate values using several methods. Among the duplicate values, their appearance in the data table can be of multiple times. In this tutorial, you will learn 4 different methods to count the order of occurrence of duplicates in Excel with ease.

**Table of Contents**hide

## Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

## 4 Methods to Count the Order of Occurrence of Duplicates in Excel

### 1. Use COUNTIF Function to Count the Order of Occurrence of Duplicates in Excel

This method can count the order of occurrence of any type of values in Excel cells such as text, numbers, etc.

In this method, we will count the order of occurrence of the contents of the **Category** column.

Anyways, here are the steps to follow:

❶ First of all, insert the following formula in cell **E5**.

`=COUNTIF($B$5:$B5, B5)`

Where,

**$B$5:$B5**is the cell range array.**B5**is the first cell of the**Category**column.

❷ Now hit **ENTER**.

❸ Drag the **Fill Handle** icon from cell **E5** to **E12**.

Finally, you will get the count of the order of occurrence of duplicates of the values stored in the **Category** column.

**Related Content: Excel Count Number of Occurrences of Each Value in a Column**

### 2. Join IF & COUNTIF Functions to Compute the Order of Occurrence of Duplicates in Excel

Now we will use a formula of the **IF **and **COUNTIF function** that does pretty much the same job as the previous method. But this time we will count the duplicates of the **Price** column instead of the **Category** column.

❶ Select cell **E5** and insert the following formula:

`=IF(COUNTIF(D:D,D5)>1,COUNTIF(D$5:D5,D5),"")`

Where,

**D:D**cell range array.**D5**is the very first cell of the**Price**column.**COUNTIF(D:D,D5)>1**calculates whether each of the values of the**Price**column exists more than once.**IF(COUNTIF(D:D,D5)>1,COUNTIF(D$5:D5,D5),””)**returns the value of**COUNTIF(D$5:D5,D5)**, if any duplicate value is found. Otherwise, it returns a null value.

❷ Now hit **ENTER** to finish inserting the formula.

❸ Now drag the **Fill** **Handle** icon to the end of the **Order** column.

After that, you will get the count of the order of the occurrence of duplicates of the **Price** column value in the **Order** column.

**Read More: How to Count Duplicates in Column in Excel (3 Ways)**

**Similar Readings:**

**Excel VBA to Count Duplicates in a Column (A Complete Analysis)****How to Ignore Blanks and Count Duplicates in Excel (3 Ways)****How to Count Duplicates Based on Multiple Criteria in Excel****VBA to Count Duplicates in Range in Excel (4 Methods)****How to Count Repeated Words in Excel (11 Methods)**

### 3. Combine IF & COUNTIF Functions to Count the Running Order of Occurrence of Duplicates in Excel

This method also uses the **IF **and **COUNTIF** functions to build a formula that only returns the count of the order of occurrence of a certain specified value.

For example, we will only count the order of occurrence of the item **“Wafer”** from the **Category** column. Now follow the steps below to do so.

❶ Insert the following formula in cell **E5**.

`=IF(B5="Wafer",COUNTIF($B$5:B5,"Wafer"),"")`

Where,

**B5=”Wafer”**refers to the item**“Wafer”.****$B$5:B5**is the cell range array.**“Wafer”**is the sample item used for comparison to check duplicate values of it.- I
**F(B5=”Wafer”,COUNTIF($B$5:B5,”Wafer”),””)**returns the order of occurrence of**“Wafer”**using**COUNTIF($B$5:B5,”Wafer”)**. If no duplicate is found it returns a null value.

❷ Now press the **ENTER** button to execute the formula.

❸ After that drag the **Fill Handle** icon from cell **E5** to **E12**.

Finally, you will see the count of the order of occurrence of **“Wafer”** as in the picture below:

### 4. Use Only IF Function to Calculate the Order of Occurrence of Duplicates in Excel

If you want a very simple formula using the **IF function** to count the order of occurrence of duplicates then use this method.

All you need to do is,

❶ First insert 1 in the top cell of the **Order** count column. Which is the default count of the first item.

❷ After that insert the following formula in the next cell i.e. cell **E6**.

`=IF(D6=D5,E5+1,1)`

Where,

**D6**is the second item of column**Price**from the top.**D5**is the top cell of the column called**Price**.**E5**is the cell containing the default count i.e. 1.

❸ Press **ENTER** button to finish entering the formula.

❹ To apply the same above formula all over the range in column **Order**, drag the **Fill Handle** icon to the end of the **Order** column.

In the end, you will get the order count of occurrence of duplicates of the items from the **Price** column as in the picture below:

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

## Conclusion

To sum up, we have discussed 4 methods to count the order of occurrence of duplicates in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website **Exceldemy** to explore more.