As the formula is copied down the column, rows where there is a value are numbered and empty rows are skipped. The IF function first checks if cell C5 has a value with the ISBLANK function: 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: 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: Same logic as above, but using ="" instead of ISBLANK. Logic reversed. If C5 is not blank, return the count, otherwise return an empty string. This version uses the not equal to operator (<>).
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.
