Create Continuous Numbering for Names with Blank Rows in WPS Spreadsheets
Question details
Create sequential numbers beside names while leaving the number cell empty whenever the corresponding name cell is blank.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Names are entered in column B, and a numbering column is needed in column A starting from row 2.
- Observed behavior
- The numbering should remain continuous across blank rows and automatically update as names are added, deleted, or moved.
Confirm which column contains the names and which row contains the first name. The formula below assumes names start in B2 and numbering starts in A2; adjust the references if your table starts elsewhere.
Use an IF and COUNTA formula to skip blank name rows
Use this method when you want a number only beside nonblank names, with continuous numbering even when blank rows appear in the list.
The IF function checks whether the current name cell is empty. COUNTA then counts all nonblank name cells from the first name row through the current row, so each entered name receives the next number.
In WPS Spreadsheets, click cell A2, the cell beside the first name cell B2.
Type =IF(B2="","",COUNTA($B$2:B2)) in A2, then press Enter.
Select A2 and drag the fill handle in the lower-right corner down through the rows where names may be entered. You can also copy A2 and paste it into the required cells in column A.
Leave a cell in column B blank to confirm its matching cell in column A stays blank, then enter a name in column B to confirm the numbering updates automatically.
Automatically Number Nonblank Names in WPS Spreadsheets
WPS Spreadsheets lets you use the IF and COUNTA functions to create a dynamic numbering column. The formula leaves number cells empty for blank name rows and recalculates the sequence when list entries change.
- 1. Open the worksheet in WPS Spreadsheets: Open your name list and identify the name column, such as column B.
- 2. Choose the first number cell: Click the cell beside the first name, such as A2.
- 3. Apply the blank-row numbering formula: Enter =IF(B2="","",COUNTA($B$2:B2)) and press Enter.
- 4. Extend the formula to later rows: Drag the fill handle from A2 down the numbering column so each row evaluates its corresponding name cell.
Frequently Asked Questions
Why does the formula leave the numbering cell blank?
The IF(B2="","",...) portion checks whether the matching name cell in column B is empty. If it is empty, the formula returns an empty result instead of a number.
How do I change the formula if my names start in another row?
Replace B2 and $B$2 with the first name cell in your list. For example, if names begin in C5 and numbers begin in B5, use =IF(C5="","",COUNTA($C$5:C5)).
Will the sequence update after I delete a name?
Yes. COUNTA recalculates the count of nonblank cells in the name range, so the remaining names are renumbered continuously after a name is deleted.
Can I use this formula in an Excel .xlsx file opened with WPS Spreadsheets?
Yes. IF and COUNTA are compatible spreadsheet functions, and the formula can be used in Excel .xlsx and .xls files in WPS Spreadsheets.
