How to Add Sequence Number by Group in Excel (2 Ways)

Method 1 – Using COUNTIF Function to Insert Sequence Number by Group

Steps:

Go to Cell C5.
Enter the COUNTIF function.
Select the range for the 1st argument. We will use the absolute reference value for the starting value of the range. And end value will be for which cell we want the sequence number.
In the 2nd argument, we will select the criteria which will be the cell for which we want the sequence number.
The formula is:

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

Using COUNTIF Function to Add Sequence by Group

Formula Breakdown

COUNTIF($B$5:B5, B5) → The COUNTIF function counts an array of cells that meets certain criteria.
$B$5:B5 → is the array.
B5 → is the criteria.
- Output: 1

Press ENTER.
We will get the sequence number for Cell B5.
Drag the Fill Handle icon from Cell C5 to C10.

Use of Fill Handle Tool

The complete Sequence No. column will be as shown in the image below.

Result of the Complete Sequence No. Column After Using Countif Function

Method 2 – Use of IF Function to Add Sequence Number by Group

We will compare our cell values with a condition and find out the sequence numbers based on the comparing values.

2.1. Using IF Function When Sequence Number Is Not Constant

Here, we will add a sequence number using the IF Function when the sequence number is not constant for the same value.

Steps:

Go to Cell C5.
Enter the IF function.
Define the condition in the 1st argument. Set a condition that Cell B5 and B4 are not equal in this cell. If the condition is TRUE, the return value will be Otherwise, the argument will add 1 with Cell C4. Here C4 is 0, as our cells start from So, the formula becomes:

=IF(B5<>B4,1,C4+1)

Applying IF Function to Add Sequence Number by Group

Press ENTER to get the sequence number for Cell B5.
Drag the Fill Handle icon from Cell C5 to C10.

Employing Fill Handle Feature

You can now see the complete Sequence No. column.

Result of the Complete Sequence No. Column After Using Countif Function

Now, get the sequence number for all the cells by the group. If our data set values are irregular, we need to sort the values by ascending or descending order. And we can autofill data in ascending or descending order.

2.2. Applying IF Function for Constant Sequence Number

We can also use the IF function to present data in another way. We can give fixed sequence numbers to every group, not to the members of the group.
For this, we inserted a row between the heading and data.

Steps:

We put 0 in cells B5 and C5.
Go to cell C6.
Enter the IF function.
Define the condition in the 1st argument. Set a condition that Cell B6 and B5 are equal in this cell. If true, the return will be Otherwise, add 1 with Cell C5. So, the formula becomes: