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:

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

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

Read More: How to Enter Sequential Dates Across Multiple Sheets in Excel

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:

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

You can now see the complete Sequence No. column.

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:

• Press ENTER to get the sequence number for cell B6.
• Drag the Fill Handle icon from cell C6 to C11.

You can now see the complete Sequence No. column.

We will get the sequence number for every group. By the sequence number, we can identify the groups easily.

Read More: How to Repeat Formula Pattern in Excel

Download Practice Workbook

Further Readings

• Applications of Excel Fill Series
• How to AutoFill Months in Excel
• How to Create Automatic Rolling Months in Excel
• How to Increment Month by 1 in Excel
• How to Autofill Days of Week Based on Date in Excel
• How to Fill Down Blanks in Excel
• How to Repeat Number Pattern in Excel
• How to Perform Predictive AutoFill in Excel

<< Go Back to Autofill Numbers | Excel Autofill | Learn Excel