How to Count the Order of Occurrence of Duplicates in Excel (4 Methods)

Method 1 – Use COUNTIF Function

This method allows you to count the order of occurrence of any type of values in Excel cells, such as text or numbers. Let’s focus on the contents of the Category column.

Follow these steps:

• In cell E5, insert the following formula:

Where,

• • $B$5:$B5 represents the cell range array.
  • B5 is the first cell in the Category column.

• Press ENTER.

• Drag the Fill Handle icon from cell E5 to E12 to get the count of duplicate occurrences in the Category column.

Related Content: How to Count Duplicates in Column in Excel

Method 2 – Combine IF & COUNTIF Functions

This method is similar to the first one but focuses on the Price column.

Here’s how:

• Select cell E5 and insert this formula:

Where,

• • D:D represents the cell range array for the Price column.
  • D5 is the first cell in 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.

• Press ENTER.

• Drag the Fill Handle icon to the end of the Order column.

You will get the count of the order of the occurrence of duplicates in the Price column value in the Order column.

Read More: How to Count Duplicates in Two Columns in Excel

Method 3 – Count Running Order of Occurrence

This method counts the order of occurrence for a specific item (e.g., Wafer) in the Category column:

• Insert this formula in cell E5:

Where,

• • B5=”Wafer” refers to the item “Wafer”.
  • $B$5:B5 is the cell range array.
  • “Wafer” is the sample item used to check for duplicates.
  • IF(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.

• Press the ENTER.

• Drag the Fill Handle icon from cell E5 to E12.

You will see the count of the order of occurrence of Wafer as in the picture below:

Read More: How to Count Duplicate Rows in Excel

Method 4 – Use Only IF Function

For a simple formula using the IF function, follow these steps:

• Insert 1 in the top cell of the Order count column (default count for the first item).
• In the next cell (E6), enter this formula:

Where,

• • D6 is the second item in the Price column.
  • D5 is the top cell of the Price column.
  • E5 contains the default count i.e. 1.

• Press ENTER.

• Drag the Fill Handle icon to the end of the Order column.

You’ll now have the order count of duplicate occurrences for items in the Price column.

Read More: How to Count Duplicate Values in Multiple Columns in Excel

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• Count Number of Occurrences of Each Value in a Column in Excel
• How to Count Duplicates Based on Multiple Criteria in Excel
• How to Count Occurrences Per Day in Excel

<< Go Back to Duplicates in Excel | Learn Excel