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:

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

Where,

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

Press ENTER.

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

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:

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

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 of the Price column value in the Order column.

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:

=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 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.