Simple statistics: counting blanks

The countblank() function does what you might expect — it counts the number of blank cells in a group that you specify. The group can consist of more than one range, such as countblank(a1:b3,a5:b7).

One obvious use for countblank() is to figure out what’s missing. For example, let’s say you’re a teacher, and you’re recording grades. Unless you go through and sort them, the papers probably won’t be in the same order as the students in your spreadsheet. Therefore, when you enter the grades, you’ll be jumping up and down and it won’t be at all obvious if someone’s been skipped. You can, of course, visually scan it and try to see if you’re missing any, but the more quickly you do that the more likely it is you’ll make a mistake. If, on the other hand, you use countblank() it will quickly and accurately tell you if there’s any grades that haven’t been entered. This will let you immediately identify who’s missing, double-check to make sure their paper didn’t just cling to someone else’s paper, and then enter 0 or do whatever else your policy is to do about late/missing submissions.

In a lot of ways, countblank() is the flip side of counta(). While counta() counts everything that’s not blank, countblank() counts everything that is. In theory, this means that =counta(A:A) would yield the same result as =rows(A:A)-countblank(A:A).

However, the difficulty is that some programs count cells that have formulas that return empty results (for example, =’ or =””) as both blank, some as non-blank, and some as both. Note that =” ” actually has a space as the result, so even though it looks empty to you, it’s not actually empty.

Here’s a quick run-down of how various programs behave:

Excel 97, 2007, 2010: Empty results count as both.