Counting Empty: Using COUNTA and COUNTBLANK in Excel


Rule number 542 of spreadsheet law: blanks are evil. 

They cause all sorts issues!  They interrupt quick-key selections of columns and rows, they cause errors when referenced by certain formulas, and they break most VBA macros unless explicitly handled.  It's in our best interest to find blanks and replace them with data wherever possible.

How do we find them?  There are many ways, among them COUNTA and COUNTBLANK.

Let's consider four columns of data.

The COUNTA function takes a range of cells and returns the number of non-blank cells in that range. 

The COUNTBLANK formula does the inverse:  it takes a range of cells and returns the number of blank cells.

Pay careful attention below to what Excel considers "blank".  A cell is NOT blank if it has errors or spaces.  The latter is particularly insidious, because if there is even one space in a cell it will be counted as non-blank. 

If you think you may have these in your spreadsheet, better to use a combinations of COUNTIF functions that looks for all the blank-like possibilities.

Need additional help with COUNTA, COUNTIF, or other Excel issues?  Reach out today via live chat, email, or phone and our experts will get you the help you need in minutes!