How to Create Sequential Numbers for Checked Rows in Excel
Question details
The user needs to dynamically generate sequential numbers in a column strictly for rows where a specific dropdown menu contains a checkmark (✓), skipping any rows that are blank or marked with an X.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an automatic numbering system in an Excel tracker or checklist based on specific cell conditions.
- Observed behavior
- The numbering sequence should incrementally advance only when a checkmark is detected, leaving non-matching rows completely blank.
Identify the exact starting cell of your checkmark column, or format your dataset as an Excel Table for easier formula referencing.
Use IF and COUNTIF Formulas with Standard Cell References
This method uses the IF function to check for the presence of a checkmark, and the COUNTIF function with an expanding range to sequentially count those marks.
By locking the starting cell of your COUNTIF range, the formula continuously expands as it moves down the column, ensuring it only counts the checkmarks up to the current row.
Click on the first cell in the column where you want the sequential numbers to be generated.
Type the formula =IF(C2="✓",COUNTIF($C$2:C2,"✓"),"") in the formula bar. Replace C2 with the actual cell reference containing your first dropdown or checkmark. Ensure the first reference in COUNTIF has dollar signs ($) to lock it.
Press Enter to confirm the formula. Then, click and drag the fill handle at the bottom-right corner of the cell downwards to apply the sequence logic to the remaining rows.

Use Structured Table References for Dynamic Updating
If your dataset is formatted as an official Excel Table, structured references provide a cleaner formula that automatically applies to new rows without manual dragging.
Create Conditional Sequential Numbers in WPS Spreadsheet
WPS Spreadsheet offers powerful, highly compatible formula processing. You can effortlessly use IF and COUNTIF statements to construct dynamic numbering lists for checklists, inventories, and forms without missing a beat.
- 1. Open your tracker: Launch WPS Spreadsheet and open your existing checklist or table document.
- 2. Select the sequence column: Click into the first cell where the row number should appear.
- 3. Input the logic formula: Type your conditional counting formula, for example: =IF(B2="✓",COUNTIF($B$2:B2,"✓"),"").
- 4. Fill down the sequence: Double-click the fill handle or drag it down to seamlessly apply the sequence to all evaluated rows.

Frequently Asked Questions
How do I type or insert a checkmark (✓) symbol into my spreadsheet?
You can insert a checkmark by navigating to the Insert tab, clicking 'Symbol', and selecting it from the list. Alternatively, you can copy the symbol from the web and paste it, or use the Windows shortcut 'Windows Key + .' to bring up the emoji keyboard and search for 'check'.
Why is my COUNTIF formula returning the same number for every checked row?
This happens when you forget to lock the starting cell in the COUNTIF range. Make sure you use an absolute reference with dollar signs for the first part of the range (e.g., $C$2:C10 instead of C2:C10) so the formula knows exactly where the sequence begins.
Can I adapt this formula to number rows containing specific text instead of a symbol?
Yes, the formula is highly adaptable. Simply replace the checkmark symbol ('✓') in both the IF condition and the COUNTIF criteria with your desired text wrapped in quotes, such as "Done", "Completed", or "Yes".




