Function Problems

How to Generate Sequential Numbers Based on a Cell Value in Excel

Natalie Taylor Oct 22, 2026 868 views

Question details

Generate sequential numbers in a column only when an adjacent cell matches a specific value, avoiding dynamic array spill errors.

How to Generate Sequential Numbers Based on a Cell Value in Excel

Product: Excel
Device & OS: not provided
Scenario: Assigning sequential IDs or numbers conditionally based on category values (e.g., 'Retail') while leaving rows with other values blank.
Observed behavior: Sequential numbers are assigned only to rows meeting the condition and increment normally, leaving non-matching rows empty without triggering spill errors.

Before you start

Ensure your dataset is organized in clear columns without merged cells, and identify the exact text condition (like 'Retail') and the starting number you want to use for your sequence.

Solution 1Recommended

Use the IF and COUNTIF Functions for Conditional Sequential Numbering

Combine the IF and COUNTIF functions to check for a specific cell value and sequentially count its occurrences, which avoids dynamic array spill errors.

This formula uses a mixed reference in the COUNTIF function to create an expanding range. It evaluates row by row, ensuring sequential numbering only for the target condition.

Select the target cell

Click on the cell where you want the first sequential number to appear (for example, O2).

Enter the formula

Type the formula: =IF(N2="retail",500+COUNTIF(N$2:N2,N2),"") into the formula bar and press Enter.

Apply to the column

Click and drag the fill handle (the small square at the bottom-right corner of the cell) down the column to apply the formula to all relevant rows.

Use the IF and COUNTIF Functions for Conditional Sequential Numbering

Customizing the Formula: Replace 'N2' with the cell reference for your specific condition, 'retail' with your exact target text, and '500' with a number one less than your desired starting sequence number.

Smart Data Processing in WPS

Generate Conditional Sequential Numbers Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and counting formulas like IF and COUNTIF. You can effortlessly manage complex data numbering and ID generation without worrying about dynamic array spill errors.

1. Open your file in WPS: Launch WPS Spreadsheet and open the dataset containing the categories you want to number.
2. Input the logic formula: Click on the target sequence cell and type =IF(N2="retail",500+COUNTIF(N$2:N2,N2),"").
3. Drag to fill: Double-click or drag the fill handle downwards to instantly generate conditional numbers across your entire list.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Intuitive formula builder and auto-complete to prevent syntax errors.Lightweight software with exceptionally fast performance for large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

How do I make the conditional sequence start from 1 instead of 501?

To start your sequence from 1, simply remove the '500+' addition from the formula. Your revised formula should look like this: =IF(N2="retail",COUNTIF(N$2:N2,N2),"").

Why is my alternative formula causing a dynamic array spill error?

Spill errors often occur when using newer dynamic array functions (like SEQUENCE or FILTER) if there is existing data blocking the cells where the array needs to expand. The IF and COUNTIF method prevents this by evaluating and outputting results strictly on a single-cell basis.

Can I generate sequences for multiple different categories simultaneously?

Yes, you can modify the approach by using nested IF statements or by removing the specific text condition to count whatever value is in the adjacent cell. For example, =COUNTIF(N$2:N2,N2) will create a separate sequence for every unique category in column N.

Excel & Data Problems