=IF(cell<>"",COUNTA(cell:cell))As you can see in the following figure, this formula works well. If you insert or delete a row, the numbers update accordingly—almost. There's a problem in row 9; if the row is empty, the formula returns FALSE. On the other hand, row 11 displays the formula's flexibility. After deleting a row (ID 5), the formula updates as required. (I purposely left the ID column so you could see these changes.)
The FALSE value is easy to eliminate by updating the IF() function:
=IF(cell<>"",COUNTA(cell:cell),"")Add any required punctuation to the IF() function as well. If you concatenate the punctuation using the following form, Excel will display the punctuation for empty rows.
=IF(cell<>"",COUNTA(cell:cell),"") & "."
Instead, add the punctuation to the IF() function as follows:
=IF(cell<>"",COUNTA(cell:cell) & ".","")
Although this formula works and seems to accommodate all the possibilities, I'm still not convinced it's the most efficient solution—do you have a better one? I'd also like to know if anyone has a use for such a formula. How might you use it?
About Susan Harkins
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.
Full Bio
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.
