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

Get FREE Advanced Excel Exercises with Solutions!

We work in Excel mostly for official and business purposes. Sometimes we need to give sequence numbers by Group. In this article, we are going to discuss how to add sequence number by group in Excel. Sequence number by group means to give sequence number to all members in a certain group.


How to Add Sequence Number by Group in Excel: 2 Ways

In the following dataset, you can see the Sales Amount and Sequence No. columns. Next using the COUNTIF and IF functions we will add sequence number by group in Excel. For smooth data presentation, first, sort the data in any order like ascending or descending order. Here, we used Excel 365. You can use any available Excel version.

Dataset to Add Sequence Number by Group


1. Using COUNTIF Function to Insert Sequence Number by Group

In this method, we will use the COUNTIF function to add sequence numbers by a group in Excel.
Here we will use the COUNTIF function to count and autofill sequence numbers of every cell in a group of our data range.

Steps:

  • In the beginning, go to Cell C5.
  • Then, Write the COUNTIF function.
  • After that, select the range for the 1st argument. Here, 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.
  • Moreover, in the 2nd argument, we will select the criteria. Here criteria will be the cell for which we want the sequence number.
  • After putting all the values our formula will be:
=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
  • At this moment, press ENTER.
  • Therefore, we will get the sequence number for Cell B5.
  • Moreover, we will pull down the Fill Handle icon from Cell C5 to C10.

Use of Fill Handle Tool

Therefore, you can see the complete Sequence No. column. Here we get sequence numbers for every group. We will have a clear view from this image that which group has how many members.

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

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


2. Use of IF Function to Add Sequence Number by Group

Here, we will compare our cell values with a condition. After that, we will find out 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:

  • In the beginning, go to Cell C5.
  • Then, write the IF function.
  • Now, 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, then 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

  • Now, press ENTER, and we will get the sequence number for Cell B5.
  • Furthermore, we will pull down the Fill Handle icon from Cell C5 to C10.

Employing Fill Handle Feature

As a result, you can 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, then first we need to sort the values by ascending or descending order. And thus 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 insert a row between the heading and data, and the process is given below.

Steps:

  • In the beginning, we put 0 in cells B5 and C5.
  • After that, go to cell C6.
  • Then, write the IF function.
  • Afterward, define the condition in the 1st argument. Set a condition that Cell B6 and B5 are equal in this cell. If true, then the return will be Otherwise, add 1 with Cell C5. So, the formula becomes:
=IF(B6=B5,C5,C5+1)

Employing IF Function for adding Constant Sequence Number for Same Values

  • Moreover, press ENTER, and we will get the sequence number for cell B6.
  • After that, pull down the Fill Handle icon from cell C6 to C11.

Use of Fill Handle Tool

Hence, you can see the complete Sequence No. column.

The outcome of the Complete Sequence No. Column

Here, 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


Practice Section

You can download the above Excel file and practice the explained methods.

Practice Section to Add Sequence Number by Group


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we explained how to put the sequence number by a group. We’ve discussed two methods with the COUNTIF and IF functions. Hope this article will be useful to you. Feel free to ask any questions regarding this topic.


Further Readings


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo