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

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 a sequence number by a group in Excel. Sequence number by group means to give sequence number to all members in a certain group.

There may be numbers or words that need to be given corresponding sequence numbers. To explain this topic we’ve made a data set of different sales amount of a shop. Now we will give them the sequence numbers.

Data set for how to add sequence number by group in Excel


Download Practice Workbook

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


2 Methods to Add Sequence Number by Group in Excel

We will discuss COUNTIF and IF functions in this article regarding the topic of sequence number by the group. For smooth data presentation, first, sort the data in any order like ascending or descending order.

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

Introduction to the COUNTIF Function

COUNTIF is a statical function. It counts the number of cells within a range with a given condition.

  • Function Objective:

Counts the number of cells within a range that meets the given condition.

  • Syntax:

=COUNTIF(range, criteria)

  • Arguments:

range The range of cells to count.

criteria The criteria that control which cells should be counted.

Steps to Use COUNTIF Function to Add Sequence Number

Here we will use the COUNTIF function to count and give sequence numbers of every cell in a group of our data range.

Step 1:

  • Go to Cell C5.
  • Write COUNTIF function.
  • Select the range for the 1st argument. Here, we will use the absolute reference value for starting value of the range. And end value will be for which cell we want the sequence number.
  • Now, 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)

Write COUNTIF function to sequence number by group

Step 2:

  • Now, press ENTER and we will get the sequence number for Cell B5.

Step 3:

  • Now, pull down the Fill Handle icon from Cell C5 to C10.

Result after applying COUNTIF function to add sequence number

Here we get sequence numbers for every group. We will have a clear view from this image that which group has how many members.


Method 2: Excel IF Function to Add Sequence Number by Group

Introduction to IF Function

The IF function is one of the most used functions in Excel. It will make a logical comparison of given data and given conditions. It mainly provides two results. If the condition is fulfilled then it returns TRUE, otherwise FALSE.

  • Function Objective:

Checks whether a condition is fulfilled, and returns one value if TRUE, and otherwise FALSE.

  • Syntax:

=IF(logical_test, [value_if_true], [value_if_false])

  • Arguments Explanation:

logical_test – Given condition for a cell or a range of cells (Compulsory).

value_if_true – Defined statement if the condition is fulfilled (Optional).

value_if_false – Defined statement if the condition is not fulfilled (Optional).

Steps to Use IF Function to Add Sequence Number

Here, we will compare our cell values with a condition. After that, we will find out sequence numbers based on the comparing values.

Step 1:

  • Go to Cell C5.
  • Write 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 C5. So, the formula becomes:
=IF(B5<>B4,1,C4+1)

 

IF function to add sequence number by group in Excel

Step 2:

  • Now, press ENTER, and we will get the sequence number for Cell B5.

Step 3:

  • Now, pull down the Fill Handle icon from Cell C5 to C10.

Result of IF function to add sequence number

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.

How to Add a Fixed Sequence Number for Every Group?

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 member of the group.

For this, we insert a row between the heading and data, and the process is given below.

Step 1:

  • Go to cell C6.
  • Write IF
  • Now, 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)

Modified IF function to add fixed sequence number for a group

Step 2:

  • Now, press ENTER, and we will get the sequence number for Cell B6.

Step 3:

  • Now, pull down the Fill Handle icon from Cell C6 to C11.

Added fixed sequence number for a group using IF function

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


Conclusion

In this article, we explained how to put the sequence number by the 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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo