Summary

To add sequential row numbers to a list of data, skipping cells that are blank, you can use a formula based on COUNTA, ISBLANK, and IF. In the example shown, the formula in B5 is:

=IF(ISBLANK(C5),"",COUNTA($C$5:C5))

As the formula is copied down the column, rows where there is a value are numbered and empty rows are skipped.

Generic formula

=IF(ISBLANK(A1),"",COUNTA($A$1:A1))

Explanation 

In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:

=IF(ISBLANK(C5),"",COUNTA($C$5:C5))

The IF function first checks if cell C5 has a value with the ISBLANK function:

ISBLANK(C5) // TRUE if empty, FALSE if not

If C5 is empty, ISBLANK returns TRUE and the IF function returns an empty string ("") as the result. If C5 is not empty, ISBLANK returns FALSE and the IF function returns COUNTA function with an expanding reference like this:

COUNTA($C$5:C5) // expanding range

As the formula is copied down, the range expands, and COUNTA returns the  "current" count of all non-blank cells in the range as defined in each row. COUNTA will count both numbers and text.

Alternatives

Both of the formulas below perform the same task, but with different syntax:

=IF(C5="","",COUNTA($C$5:C5))

Same logic as above, but using ="" instead of ISBLANK.

=IF(C5<>"",COUNTA($C$5:C5),"")

Logic reversed. If C5 is not blank, return the count, otherwise return an empty string. This version uses the not equal to operator (<>).

Related formulas

Excel formula: Automatic row numbers

Automatic row numbers

In this example, the goal is to create automatic row numbers starting in cell B5 that match the data entered in column C. When new data is added to the list, the row numbers should increase as required. If items are deleted, the row numbers should respond accordingly. This has traditionally been a...
Excel formula: Automatic row numbers in Table

Automatic row numbers in Table

When no argument is provided, the ROW function returns the "current row", that is, the row number of the cell that contains it. When a cell reference is provided, ROW returns the row number of the cell. When a range is provided, ROW returns the first row number in the range. In the example shown,...
Excel formula: Last n rows

Last n rows

The core of this formula is based on another formula that calculates the "current row" in a range: =ROW()-ROW(rng.firstcell)+1 In brief, we get the current row in the workbook, then subtract the first row number of the range plus 1. The result is an adjusted row number that begins at 1. The INDEX...
Excel formula: Get relative row numbers in range

Get relative row numbers in range

The first ROW function generates an array of 7 numbers like this: {5;6;7;8;9;10;11} The second ROW function generates an array with just one item like this: {5} which is then subtracted from the first array to yield: {0;1;2;3;4;5;6} Finally, 1 is added to get: {1;2;3;4;5;6;7} Generic version with...
Excel formula: First row number in range

First row number in range

When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range that contains multiple rows, the ROW function will return an array that contains all row numbers for the range. In the example shown the array looks like this: {5;6;7;8;9;10}...
Excel formula: Last row number in range

Last row number in range

When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range with multiple rows, the ROW function will return an array that contains all row numbers for the range: {5;6;7;8;9;10} To get only the first row number, we use the MIN function...
Excel formula: First column number in range

First column number in range

When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers for the range. In the example shown the array looks like this...
Excel formula: Basic outline numbering

Basic outline numbering

At the core, this formula builds a level 1 and level 2 number and concatenates the two numbers together with a period (".") as a separator. The result is a value like "1.1". The "level 1" number is generated with COUNTA like this: =COUNTA($B$5:B5) Note the range is an expanding reference , so it...

Related functions

Excel COUNTA function

COUNTA Function

The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.
Excel IF function

IF Function

The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical...
Excel ISBLANK function

ISBLANK Function

The Excel ISBLANK function returns TRUE when a cell is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions

Training