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.
- 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:
- Select cell D11 and enter the following formula:
- Select cell D15 and enter the following formula:
- 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:
🔎 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.
- 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.
- 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:
🔎 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.
- 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:
🔎 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.
- Select cell D5 and enter the following formula:
- 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
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.