Auto Serial Number in Excel Based on Another Column

Putting Auto Serial number can seem easy, but not when you are introduced to different types of scenarios. Situations like having blank cells in the sheets, and adding Serial numbers according to criteria can make the process hectic. If you are interested to learn how you can still add Auto Serials in Excel based on another column, this article may come in handy for you. In this article, we are going to show how you can add Auto Serial number in Excel based on another column with elaborate explanations.

Auto Serial Number in Excel Based on Another Column: 4 Suitable Examples

We presented 4 different and unique real-life examples of inputting Serial numbers automatically in an Excel sheet, based on the value in another column. We used a variety of functions and different types of scenarios where we would need to use these examples

1. Combination of COUNTIF and IF Functions

The SUM, IF and COUNTIF functions will help us to create auto serial numbers with criteria.

Steps

In the beginning, insert a new row between rows 6 and 7
And also insert an additional row in between rows 9 and 10, 12 and 13.
Finally, they would look like this.

Now select cell D7 and enter the following formula:

=SUM(D5:D6)

Select cell D11 and enter the following formula:

=SUM(D8:D10)

Select cell D15 and enter the following formula:

=SUM(D12:D14)

Then they would look like this.

Then enter 1 beside the first item in which we want to Serial.

Right after that, select cell B6 and enter the following formula:

=IF(@$C$5:$C$15="Total","",COUNTIF($B$5:B5,">0")+1)

🔎 Formula Breakdown

COUNTIF($B$5:B5,”>0″): This function will count each entry if it is greater than 0, In the range of cells, starting from the topmost cell to each cell.$B$5 denotes the topmost or the first entry in the range. The COUNT function will count entries starting from this cell.
IF(@$C$5:$C$15=”Total”,””,COUNTIF($B$5:B5,”>0″)+1): The count of each individual entry will happen only when it pass the condition set up by the IF function. Here, the IF function first checks, whether there is any text called Total in the range of cells C5:C15. If there aren’t any, the COUNTIF function will continue to do its job of counting entries. Otherwise, the COUNTIF function will skip that row and move to the next row continuing counting.

Entering this formula will Serial the products until they find the Total.
Drag the Fill Handle to cell B15.
Doing that will fill the range of cell B5:B15 with the Serial number.

💬 Note

In some versions of Excel, other than the Microsoft 365 edition. This function can show a SPILL error. To solve this, try removing only the @ sign in the formula. Doing this could solve the issue.

2. Implementing ROW and SEQUENCE Functions

With the combinations of the ROW and SEQUENCE functions, we can make the process of adding automatic serial number.

Steps

We are going to add Serial Automatically to these Peoples in the Name Column.
And this Serial will be Automatic and will depend on the cell E5 and
Because in these two cells we have the Serial no of the first cell and the increment of the Serial in each step.

Then select cell B5 and enter the following formula:

=SEQUENCE(ROWS(B5:B12),1,F4,F5)

🔎 Formula Breakdown

ROWS(B5:B12): This will return the number of rows in the range of cells B5:B12.
SEQUENCE(ROWS(B5:B12),1,F4,F5): The SEQUENCE function will create a sequence or list of numbers. Whose row number will be equal ot the output of the ROW function. And starting number of the Serial would be the value in cell F4 and the increment in which the Serial will increase will be the value in cell F5.

Immediately after you enter the formula, you will notice that cell B5 now has Serial no 1.
And also at the same time, the whole range of cell B5:B12 will be filled with the Serial no with 1 increment.

3. Combining IF, ISBLANK and COUNTA Functions

Implementation of functions like IF, ISBLANK, and COUNTA, we can avoid the blank cells and put Serial numbers effectively.

Steps

If you notice, that there is a blank cell in the range of cells B5:C15. And we want to add the Serial number Automatically on it.
If we want to choose the traditional approach, then the blank cell will be counted into it, which actually bears no meaning.
To avoid the blank cells and Serial the names, first select cell D5 and enter the following formula:

=IF(ISBLANK(C5)," ",COUNTA($C$5:C5))

🔎 Formula Breakdown

COUNTA($C$5:C5): COUNTA function will calculate the nonblank cell values.
ISBLANK(C5): It will return logical True if cell C5 is blank.
IF(ISBLANK(C5),” “,COUNTA($C$5:C5)): This part of the function will calculate whether cell C5 is blank or not. If the return from the ISBLANK is True, then it will place nothing in that cell. Otherwise, it will continue calculating the non-blank cell values.

The drag the Fill Handle to cell D15.
Doing this will skip the blank cells and Serial the people’s names.

4. Auto Serial Number Based on Criteria

In this example, we will try to Serial column based on their grouping. That means, if there is multiple entries of the same place of birth, we will Serial them accordingly. We also going to use the COUNTIF function in this case.

Steps

Select cell D5 and enter the following formula:

=COUNTIF(C$5:C5,C5)

Then drag the Fill Handle to cell D12.
Now the range of cells D5:D12 is now filled with the Serial numbers. These Serial numbers are depending upon the Place of birth.
For the same place of birth, they increment the Serial by one. Otherwise, it will start by putting 1.