You can use several formulas (both lambda and non-lambda) to number rows dynamically while skipping blanks in Google Sheets. Assume you have text in column B (range B2:B), and you want to number rows in column A (range A2:A) corresponding to B2:B. If there are blank cells in column B, the numbering should skip those rows but continue the sequence in the next row.

The shortest formula to skip blank rows in sequential numbering is the following MAP lambda formula:

=MAP(range, LAMBDA(Σ, IF(Σ="", , COUNTA(range_start:Σ))))

When using this formula, replace range with the range for which you want dynamic numbering while skipping blanks, and replace range_start with the cell reference of the range’s start.

Example of Auto Numbering Rows While Skipping Blank Cells in Google Sheets

In the following example, I have a list of names in B2:B. Use the following formula in cell A2 after clearing A2:A:

=MAP(B2:B, LAMBDA(Σ, IF(Σ="", , COUNTA(B2:Σ))))

This formula effectively numbers rows while skipping blank rows.

Formula Explanation

The easiest way to explain the above formula is by using the following non-array formula:

=IF(B2="", , COUNTA($B$2:B2))

You enter this formula in cell A2 and drag it down to get the same result.

The COUNTA function returns the count of values in column B up to the current row, ignoring blank cells.

We convert this to an unnamed LAMBDA function as follows to apply this to each row in the array B2:B using MAP:

LAMBDA(Σ, IF(Σ="", , COUNTA(B2:Σ)))

Where Σ is the current element in the array.

Other Methods for Auto Numbering Rows While Skipping Blanks

The above formula is the shortest and simplest way to skip blanks in sequential numbering in Google Sheets. However, since it’s a lambda-based formula, it can be resource-intensive when applied to a large dataset. Here are two non-lambda alternatives:

1. COUNTIFS-Based Formula

=ArrayFormula(IF(B2:B="",,COUNTIFS(B2:B,"<>", ROW(B2:B), "<="&ROW(B2:B))))

The COUNTIFS formula counts the values in the range B2:B based on two conditions:

• "<>": Skips blank cells.
• "<="&ROW(B2:B): Ensures the row numbers in B2:B are less than or equal to the current row number.

This returns a running count of non-empty cells, which results in sequential numbering while skipping blank rows dynamically.

2. VLOOKUP and FILTER-Based Formula

=ArrayFormula(IFNA(VLOOKUP(ROW(B2:B), {FILTER(ROW(B2:B), B2:B<>""), SEQUENCE(COUNTA(B2:B), 1)}, 2, 0)))

{FILTER(ROW(B2:B), B2:B<>""), SEQUENCE(COUNTA(B2:B), 1)} creates a range for vertical lookup, where the first column contains the row numbers of the non-empty cells and the second column contains the corresponding sequential numbers.

The VLOOKUP formula searches for the row number in the range and returns the corresponding sequence number.

Additional Tip: Using SCAN for Dynamic Row Numbering

SCAN is one of the lambda functions in Google Sheets used for running totals because it can return intermediate values in calculations, storing them in the accumulator.

We can use it to number rows dynamically while skipping blank cells:

=ArrayFormula(IF(B2:B="",,SCAN(0, B2:B, LAMBDA(a, v, IF(v="", a, NOT(ISBLANK(v))+a)))))

This formula returns the accumulator value (a) if the current element (v) is blank. If the current element is not blank, it adds 1 to the accumulator (NOT(ISBLANK(v)) + a), where NOT(ISBLANK(v)) returns 1 for non-blank cells.

The IF logical test at the beginning helps remove the numbers from empty rows, preventing them from repeating the row number from the row above.

Resources

• Insert Sequential Numbers Skipping Hidden | Filtered Rows in Google Sheets
• Adding N Blank Rows to SEQUENCE Results in Google Sheets
• Date Sequence Every Nth Row in Excel (Dynamic Array)
• Auto Serial Numbering in Google Sheets with Row Function
• Group-Wise Serial Numbering in Google Sheets
• Group-Wise Dependent Serial Numbering in Google Sheets