Create a numbered list ignoring blank cells
The formula in column B returns a running count based on values in column C.
Formula in cell B3:
The formula consists of two Excel functions. The IF function checks if the corresponding value in column C is not empty.
If cell C3 is not empty the COUNTA function counts the number of cells in cell range $C$3:C3 that are not empty.
Cell range $C$3:C3 has one cell that is not empty so the formula returns 1 in cell B3.
Note that cell reference $C$3:C3 expands when you copy cell B3 and paste to cells below.
For example, in cell B4 the cell reference changes to $C$3:C4.
If cell C3 is empty the formula returns nothing.
Download Excel *.xlsx file
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]
How to quickly select a non contiguous range
A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]
How to find errors in a worksheet
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]
The picture above shows data in column B, some cells contain nothing, they are blank. I will now go through […]
How to replace part of formula in all cells
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
Convert column number to column letter
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, […]
How to quickly select a cell range
Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]
If your cell text is taking to much space Excel allows you to rotate text in any angle. Here are […]
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
