Function Problems

How to Create Consecutive Numbers After Incomplete Rows in Excel

Chanuka Geekiyanage Sep 28, 2026 869 views

Question details

The user needs to generate consecutive participant numbers in a column but wants the count to skip rows where the participant's data is incomplete.

How to Create Consecutive Numbers Skipping Incomplete Rows

Product: Spreadsheet
Device & OS: not provided
Scenario: Assigning consecutive positions or IDs to participants while explicitly excluding rows that contain incomplete entries or blank cells.
Observed behavior: Using the standard ROW() function assigns the physical worksheet row number, which incorrectly counts incomplete and blank rows instead of dynamically skipping them.

Before you start

Ensure your dataset has a specific column (such as Column C) that clearly indicates whether a row is complete with data or incomplete (e.g., left blank).

Solution 1Recommended

Use IF and COUNTA to Number Completed Rows

This method dynamically counts non-blank cells in a reference column to assign consecutive numbers only to completed rows.

Instead of using the ROW() function which generates static row numbers, combining the IF function with an expanding COUNTA range allows the spreadsheet to evaluate if a row is complete before assigning it the next consecutive integer.

Identify the reference column

Determine which column indicates completion. For this example, assume Column C contains data for completed participants.

Select the starting cell

Click on the first cell in the column where you want the consecutive numbers to appear (for example, cell A2).

Enter the expanding formula

Type the formula =IF(C2="","",COUNTA($C$2:C2)) into the formula bar and press Enter. This tells the sheet to leave the cell blank if C2 is blank, or count the number of non-blank cells from C2 down to the current row.

Copy the formula down

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

Use IF and COUNTA to Number Completed Rows

Expanding Range Reference: The use of the dollar signs in $C$2 locks the starting cell, creating an expanding range that correctly increments the count as you drag the formula downward.

Solution 2

Use COUNTIFS for Specific Incomplete Text Markers

If your incomplete rows are marked with specific text (like "---") instead of being blank, you should use COUNTIFS to dynamically skip those specific values.

Seamless Spreadsheet Numbering

Manage Spreadsheet Data Effortlessly with WPS Office

You can easily apply advanced formulas like IF, COUNTA, and COUNTIFS to manage consecutive numbering and data sorting in WPS Spreadsheet. It offers a highly compatible and lightweight environment for all your daily data analysis tasks.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your participant data.
2. Input the dynamic formula: Select the first cell in your ID column and enter the formula =IF(C2="","",COUNTA($C$2:C2)).
3. Fill the series: Press Enter, then drag the fill handle downward to instantly generate your consecutive numbering.

Fully compatible with Microsoft Excel formulas and functions.Intuitive interface for applying drag-and-fill sequences.Free to use with a lightweight, fast installation.Built-in error checking to help prevent circular reference issues.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the ROW() function to skip blank rows?

No, the ROW() function strictly returns the physical worksheet row number regardless of the cell's content. To skip blanks and maintain a sequential count, you must use a conditional counting function like COUNTA combined with IF.

Why is my formula returning a circular reference error?

A circular reference occurs if your formula in a column (e.g., Column A) refers back to itself in the calculation. Ensure your IF and COUNTA functions only refer to the column containing the completion status (e.g., Column C).

How do I start the numbering at a number other than 1?

You can add a fixed number to the end of your formula. For example, to start at 100, use =IF(C2="","",COUNTA($C$2:C2)+99). This will output 100 for the first completed row and increment consecutively from there.

Excel & Data Problems