How to Use COUNTBLANK in Excel

Microsoft Excel, Microsoft Office

Some of the Excel functions, such as IF, come into play all the time, even outside business. They’re versatile and can do a lot. But others seem a little abstruse, or out in left field.

One question I occasionally get from business people in Excel is something like, “How do I make sure someone has filled in all the cells where I need data to, say, calculate an average correctly?” (Think “tax form” or W-4, for example. You want to make sure certain spaces are filled in regardless.)

In some situations, you might use a function like ISERROR, and incorporate it with IF to test if there IS an ERROR when you perform a calculation:

=IF(ISERROR(AVERAGE(D2:D10))…

…so that if performing the calculation glitches, you can have the formula show a message to this effect.

=IF(ISERROR(AVERAGE(D2:D10)),”There’s a blank cell there”…

Otherwise, perform the calculation.

=IF(ISERROR(AVERAGE(D2:D10)),”There’s a blank cell there”,AVERAGE(D2:D10))

 

But this doesn’t quite solve the problem, because even if just one of the cells has data in it, there won’t be an error as far as Excel is concerned.

 

So we need to call a function that looks for ANY cells being empty, and lets us know. It’s called COUNTBLANK. (Sounds like some kind of strange nobleman–Count Blank, from some tiny hamlet somewhere….)

 

=IF(COUNTBLANK(D2:D10)>0,”Missing Data in D2 thru D10″,AVERAGE(D2:D10))

 

What it does is pretty simple, though. It COUNTs the BLANKs in a range, and can let us know how many there are. IF (as you see in the above formula) there are any (“COUNTBLANK(D2:D10)>0”), we want to see the TRUE result from the IF (the message about missing data); if not–that is, if the test is FALSE–then we want it to calculate the average. We’ve told the program to perform the calculation only IF the COUNT of BLANK cells is NOT greater than 0, and let us know if there are any blanks so we (or the user) can correct this.

 

Of course, guaranteeing that only numbers get filled in is another matter. But there are a few different ways to take care of this, such as Data Validation. The important thing is, MISSING data are a problem, as the result isn’t an accurate one. And letting the user know about this is the big thing.

 

Be sure to check out our courses on Microsoft Excel to get more info.