Sometimes we have a data set of repeated numbers that we wish to categorize and assign a sequence number to. In this article, we will learn four approaches to add excel sequence number by group.

## 📁 Download Excel File

Download the given excel file for practice.

## Learn to Add Sequence Number by Group in Excel with These 4 Approaches

Here we will use the following sample datasheet to group the data and give a sequence number by group.

**Approach**

### 1. Applying COUNTIF Function

In this first method, we will use the **COUNTIF function** to add sequence number by group.** **

**⬇️⬇️ STEPS ⬇️⬇️**** **

- First, select cell
**D6**. - After that, enter the following formula.

**=COUNTIF($C$6:C6,C6)**

- Then press the
**Enter**key.

- And after that drag the
**Fill Handle**icon from cell**D6**to**D11**.

Here we can see that depending on the number of goals scored by each player, it grouped the data and gave a sequence number to each group.

**📕 Read More: 5 Ways to Fill Down to Last Row with Data in Excel**

**Approach**

### 2. Utilizing IF Function

In this method, we are going to use the **IF function** to add sequence number by group.

**⬇️⬇️ STEPS ⬇️⬇️**

- First select cell
**D6**. - Then enter the following formula.

**=IF(C6<>C5,1,D5+1)**

- Press the
**Enter**key.

- Now select cell
**D6**and drag the**Fill Handle**icon from**D6**to**D11**.

And we got the sequence number by group. But in this method, if the dataset is irregular then we have to sort the data in ascending or descending order before applying this method.

**📕 Read More: 3 Ways to Auto Number or Renumber After Filter in Excel**

**Approach**

### 3. Adding Fixed Sequence Number Using IF function

Now we can also add the sequence number in a different way by using the same **IF** function. In this method, we can give a fixed sequence number to each group. And all the members of one group will get the same number.

** ⬇️⬇️ STEPS ⬇️⬇️ **

- Firstly, we have to insert a new
**Row**between the header and the data.

- Then we have to select cell no
**D7**. - After that enter the following formula.

**=IF(C7=C6,D6,D6+1)**

- Then press the
**Enter**key.

- Then again select cell
**D7**and drag the Fill handle to**D12**and we will get the following result.

We can observe the change now if we compare the result to the prior result. Instead of assigning distinct sequence numbers to group members, this method assigns a single sequence number to all group members. For example, when Mbappe and Messi both scored the same amount of goals, the method grouped them and assigned them the same sequence number.

**📕 Read More: 4 Ways to Repeat Rows a Specified Number of Times in Excel**

**Approach**

### 4. Implementing RANK Function

In this method, we will use the **RANK function** to assign a sequence number to the group members in excel.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select cell
**D6.** - Then enter the following formula.

**=RANK(C6,$C$6:$C$11,0)**

- Then press the
**Enter**key.

- Now select cell
**D6**and drag down the Fill Handle icon from**D6**to**D11**.

Here we can see that the **RANK** function assigns the same sequence number to the duplicate numbers but the difference between method 3 and method 4 is the presence of duplicate numbers affecting the subsequent numbers. For example, when Mbappe and Messi both scored the same number of goals, the **RANK **function assigned each of them the same sequence number, which is 1. Then, for Rashford, a sequence number of 3 was allocated. There is no player with a sequence number of 2.

**📕 Read More: 6 Ways to Autofill Sequential Letters in Excel**

## Takeaways from This Article

📌 In this article we illustrated four ways to add sequence number by group in excel.

📌 In the first method we used the **COUNTIF** function and in the second method we used **IF** function to assign excel sequence number by group

📌 In the third method, we have used **IF** function to assign fixed sequence number by group.

📌 Finally in the fourth method we used** RANK** function to rank the data.

## Conclusion

Finally, we have come to the end of this article. In this article, we tried to provide every possible way to assign excel sequence number by group. As many methods have been demonstrated here, you may quickly choose which one is suitable for you. If you have any questions, please leave them in the comments. Finally, we propose that you visit **Excelden** to discover more about Excel.

## Related Articles

**6 Ways to Fill Down to Last Row with Data Not Blank in Excel****7 Simple Ways to Increment Month by 1 in Excel**