**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.

## Download Practice Workbook

Download this practice workbook below.

## 4 Suitable Examples to Create Auto Serial Number in Excel Based on Another Column

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 add **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.

**Read More: ****How to Create a Number Sequence in Excel Based on Criteria**

### 2. Implementing ROW and SEQUENCE Functions

With the combinations of the **ROW** and **SEQUENCE** functions, we can make the process of adding **Serial** number **Automatic**.

**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.

**Read More: ****How to Increment Row Number in Excel Formula (6 Handy Ways)**

### 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.

**Read More: ****How to Create a Number Sequence with Formula in Excel**

### 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.

**Read More: ****How to Add Automatic Serial Number with Formula in Excel**

## Conclusion

Here, we have answered the question” how you **Auto** **Serial** number in Excel based on another column” with elaborate explanations.

For this problem, a workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the **Exceldemy** community will be highly appreciable.