Function Problems

Excel: Number Yes Values Sequentially and Skip No Values

Anum Mushtaq Nov 12, 2026 868 views

Question details

Create a running sequence that assigns a number only to cells marked Yes and leaves corresponding No rows blank.

Excel: Number Yes Values Sequentially While Skipping No Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: The source values are in one column, and the user needs a sequential count in an adjacent column.
Observed behavior: Each Yes value should receive the next number in the sequence, while each No value should return a blank cell.

Before you start

Confirm that the source cells use the exact text Yes and No without extra spaces. Adjust the formula column references if your Yes/No values are not in column A.

Solution 1Recommended

Use IF and COUNTIF for a running Yes sequence

Use this option for standard Excel worksheets when each row should be numbered only if its source cell contains Yes.

The COUNTIF range expands as you fill the formula down, so it counts all Yes values from the first data row through the current row. IF returns a blank for rows containing No.

Enter the formula in the first result cell

If your Yes/No values begin in A1, select B1 and enter =IF(A1="Yes",COUNTIF($A$1:A1,"Yes"),"").

Confirm the formula

Press Enter. A row with Yes displays 1 for the first matching entry, while a row with No remains blank.

Fill the formula down

Select B1 and drag the fill handle down, or double-click the fill handle to copy the formula alongside your data.

Use IF and COUNTIF for a running Yes sequence

Expected result: The sequence increases only for Yes rows: Yes returns 1, the next Yes returns 2, and No rows stay blank.

Solution 2

Use a dynamic array formula for an Excel 365 range

Use this option in Excel 365 when you want one formula to calculate running counts for a defined data range.

Use WPS Spreadsheets for running counts

Create a Running Yes Count in WPS Spreadsheets

WPS Spreadsheets can create the same sequential numbering pattern with an IF and COUNTIF formula. Enter the formula beside your Yes/No data, then fill it down to number only the Yes entries and leave No rows empty.

1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file in WPS Office, then select the cell beside the first Yes/No value.
2. Enter the running-count formula: For values beginning in A1, type =IF(A1="Yes",COUNTIF($A$1:A1,"Yes"),"") in B1.
3. Apply the formula: Press Enter to calculate the first result.
4. Fill the formula through the data: Drag the fill handle from B1 down the result column to number every Yes row.

Counts only rows containing Yes and leaves No rows blankUses familiar IF and COUNTIF spreadsheet functionsCompatible with Microsoft Excel .xlsx and .xls filesProvides a lightweight interface for editing existing Excel worksheets

microsoft office alternative - wps office

Frequently Asked Questions

Why are my No rows showing a number instead of a blank?

Check the IF condition in the formula. It should end with ,"") so Excel returns an empty text value when A1 is not equal to "Yes".

How do I start the Yes sequence from a row other than row 1?

Use your actual first data row as the starting absolute reference. For data starting in A5, enter =IF(A5="Yes",COUNTIF($A$5:A5,"Yes"),"") in the adjacent result cell.

Can the formula count values regardless of uppercase or lowercase letters?

Yes. COUNTIF and the equal comparison used here are not case-sensitive, so Yes, YES, and yes are treated as matches.

How can I stop numbering after 110 Yes entries?

Wrap the count result in an additional IF condition, for example: =IF(A1="Yes",IF(COUNTIF($A$1:A1,"Yes")<=110,COUNTIF($A$1:A1,"Yes"),""),"").

Can I use this formula in WPS Spreadsheets?

Yes. WPS Spreadsheets supports the IF and COUNTIF functions used in =IF(A1="Yes",COUNTIF($A$1:A1,"Yes"),"").

Excel & Data Problems