Function Problems

How to Create Sequential Numbers for Checked Rows in Excel

John WilsonJohn Wilson Nov 10, 2026 869 views

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.

How to Create Sequential Numbers for Checked Rows in Excel
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.
Before you start

Identify the exact starting cell of your checkmark column, or format your dataset as an Excel Table for easier formula referencing.

Solution 1Recommended

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.

1
Select the target cell

Click on the first cell in the column where you want the sequential numbers to be generated.

2
Enter the conditional formula

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.

3
Apply to the entire column

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 IF and COUNTIF Formulas with Standard Cell References
Expanding Ranges: The expression $C$2:C2 creates an expanding range. As you drag the formula down, it becomes $C$2:C3, $C$2:C4, etc., counting occurrences from the top every time.
Efficient Data Management with WPS Spreadsheet

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. 1. Open your tracker: Launch WPS Spreadsheet and open your existing checklist or table document.
  2. 2. Select the sequence column: Click into the first cell where the row number should appear.
  3. 3. Input the logic formula: Type your conditional counting formula, for example: =IF(B2="✓",COUNTIF($B$2:B2,"✓"),"").
  4. 4. Fill down the sequence: Double-click the fill handle or drag it down to seamlessly apply the sequence to all evaluated rows.
100% compatible with Microsoft Excel formulas and .xlsx files.Supports structured table referencing and dynamic expanding ranges.Lightweight, fast-loading, and completely free to use.
microsoft office alternative - wps office

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".