How to Automatically Insert Serial Numbers in Excel Based on Another Column (4 Examples)

Example 1 – Combination of COUNTIF and IF Functions

To create auto serial numbers with specific criteria, we’ll use the SUM, IF, and COUNTIF functions.

Steps:

Insert a new row between rows 6 and 7.
Insert additional rows between rows 9 and 10, and 12 and 13.
Your sheet will now look like this:
Next, select cell D7 and enter the following formula:

=SUM(D5:D6)

Similarly, enter the formulas for cells D11 and D15:

=SUM(D8:D10)

=SUM(D12:D14)

Your sheet will now appear as follows:

Beside the first item where you want to start the serial numbers, enter 1.

Then, 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.

This formula will generate serial numbers until it encounters the Total value.
Finally, drag the Fill Handle down to cell B15 to fill the range B5:B15 with serial numbers.

Now you’re all set! Your Excel sheet will have auto-generated serial numbers based on the specified criteria.

Note:

In certain versions of Excel, other than the Microsoft 365 edition, this function may display a SPILL error. To resolve this issue, consider removing only the “@” sign from the formula. Doing so might resolve the problem.

Example 2 – Implementing ROW and SEQUENCE Functions

By combining the ROW and SEQUENCE functions, we can create an automated serial numbering process. Let’s break down the steps:

We want to automatically assign serial numbers to people in the Name column.
The serial number will be automatic and will depend on the value in cell E5.
The first cell already contains the initial serial number, and subsequent cells will increment the serial number.

To achieve this, select cell B5 and enter the following formula:

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

- ROWS(B5:B12): This calculates the number of rows in the range B5:B12.
- SEQUENCE(ROWS(B5:B12), 1, F4, F5): The SEQUENCE function generates a list of numbers, with each row number corresponding to the output of the ROW function. The starting serial number is taken from cell F4, and the increment is based on the value in cell F5.

After entering the formula, you’ll notice that cell B5 now displays Serial Number 1. Simultaneously, the entire range from B5 to B12 will be filled with serial numbers incremented by 1.

Example 3 – Combining IF, ISBLANK and COUNTA Functions

By utilizing functions like IF, ISBLANK, and COUNTA, we can effectively handle blank cells and assign serial numbers. Let’s break down the steps:

Identify Blank Cells:
- If you notice a blank cell within the range of cells B5:C15, we want to automatically assign a serial number to it.
- However, using the traditional approach would count the blank cell, which doesn’t serve any meaningful purpose.

Avoiding Blank Cells:
- To avoid counting blank cells and correctly assign serial numbers to names, follow these steps:
  - First, select cell D5.
  - Enter the following formula:

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

Let’s break down the formula components:
- COUNTA($C$5:C5): COUNTA: The COUNTA function calculates the number of non-blank cell values in the range.
- ISBLANK(C5): This function returns True if cell C5 is blank.
- IF(ISBLANK(C5),” “,COUNTA($C$5:C5)): This part of the function checks whether cell C5 is blank. If it is, it places nothing in that cell. Otherwise, it continues calculating the non-blank cell values.

Applying the Formula:
- Drag the Fill Handle from cell D5 to cell D15.
- This approach ensures that blank cells are skipped, and serial numbers are assigned to people’s names accordingly.

Example 4 – Automatic Serial Number Based on Criteria

In this example, we’ll assign serial numbers based on grouping within a column. Specifically, if there are multiple entries with the same place of birth, we’ll assign serial numbers accordingly. We’ll achieve this using the COUNTIF function.

Steps:

Selecting the Cell:
- Begin by selecting cell D5.

Formula Entry:
- Enter the following formula in cell D5:

=COUNTIF(C$5:C5,C5)

Let’s break down the formula:
- C$5:C5: This range covers all cells from C5 to the current row (D5).
- C5: Refers to the value in cell C5 (the place of birth).

Applying the Formula:
- Drag the Fill Handle from cell D5 to cell D12.
- As a result, the range of cells D5:D12 will now be filled with serial numbers.
- These serial numbers depend on the place of birth:
  - For the same place of birth, the serial number increments by one.
  - Otherwise, it starts with 1.