Function Problems

How to Create Sequential Numbers Based on Product Names in Excel

Chanuka Geekiyanage Oct 7, 2026 871 views

Question details

The user needs to generate a sequential number in one column only when a corresponding product name exists in another column, ensuring the numbering increases for populated rows while skipping blank rows.

How to Create Sequential Numbers Based on Product Names in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic numbered list that automatically increments based on data presence in an adjacent column.
Observed behavior: Generating an unbroken sequence of numbers alongside populated rows while leaving corresponding cells empty for blank rows.

Before you start

Identify the column containing your product names and determine the starting row for your data. Ensure there are no merged cells in your numbering or product columns, as this can cause formula errors.

Solution 1Recommended

Use the IF and COUNTIF Functions to Generate Sequential Numbers

This is the most reliable method for creating a running count of non-blank cells that automatically skips empty rows.

By combining the IF and COUNTIF functions, you can instruct Excel to check if a cell contains data before assigning it a number. If the cell is empty, it returns a blank. If it contains text, it counts all preceding non-empty cells to generate the next number.

Select the starting cell

Click on the first cell in your numbering column (e.g., A2) where you want the sequence to begin.

Enter the formula

Type the formula =IF(B2<>"",COUNTIF($B$2:B2,"<>"),""), replacing 'B2' with the cell containing your first product name.

Apply and drag

Press Enter to apply the formula. Then, click and drag the fill handle at the bottom right of the cell to copy the formula down to the rest of your rows.

Use the IF and COUNTIF Functions to Generate Sequential Numbers

Understanding Absolute References: The use of the dollar signs ($B$2) locks the starting cell of the COUNTIF range, allowing the range to expand dynamically as you copy the formula down the column.

Solution 2

Alternative: Use IF and COUNTA Functions

Similar to COUNTIF, the COUNTA function can be used to count non-empty cells in a dynamic range to create sequences.

Easily Manage Formulas and Sequential Data with WPS Office

WPS Spreadsheets provides robust support for all standard Excel functions like IF, COUNTIF, and COUNTA, making it incredibly simple to generate dynamic sequential numbers and manage large datasets.

1. Open your dataset: Launch WPS Office, open your spreadsheet, and select the first cell for your sequence.
2. Input the dynamic formula: Type =IF(B2<>"",COUNTIF($B$2:B2,"<>"),"") into the formula bar.
3. Populate the sequence: Press Enter, then drag the fill handle down to apply it to your entire dataset automatically.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Intuitive interface for tracking cell references and debugging formulas.Lightweight software with fast processing for large data ranges.Free built-in templates for inventory and product management.

Frequently Asked Questions

Why is my formula returning the same number for every row?

This usually happens if you forget to lock the starting cell reference in your COUNTIF formula. Ensure the first part of your range uses absolute referencing with dollar signs, like $B$2:B2, so the range expands properly as it goes down.

How can I start the sequence from a number other than 1?

You can modify the formula by adding a constant value at the end of the COUNTIF function. For example, to start at 10, use =IF(B2<>"",COUNTIF($B$2:B2,"<>")+9,"").

Will this formula work if my product names contain numbers instead of text?

Yes, the condition COUNTIF(...,"<>") counts any non-blank cells, including both text and numeric values, so your sequence will still generate correctly regardless of the data type.

Why am I getting a #NAME? error in my formula?

The #NAME? error occurs when there is a typo in the function name or a missing quote. Check your formula to ensure you have spelled IF and COUNTIF correctly, and verify there are no spaces in the function names or missing quotation marks around the "<>" criteria.

Excel & Data Problems