Exceljet

Quick, clean, and to the point

Add row numbers and skip blanks

Excel formula: Add row numbers and skip blanks
Generic formula 
=IF(ISBLANK(A1),"",COUNTA($A$1:A1))
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.

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 (<>).

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.