Count the number of blank values in a range in Excel

I created a podcast episode about this Excel trick.

If you want to count the number of cells with blank values in them, you may think it’s as simple as using the COUNTBLANK() formula. Usually you do this if you want to know how much data is missing in your data set. Say you have a list of 10 students, and if a student is missing from the set, you want to quickly count how many students are missing from your data range.

However, using COUNTBLANK in this manner only works if you have the exact cell references for your range of data (e.g. “A1:A20”). What if you don’t know the exact cell range? In this episode, I talk about a small trick I learned about how to count the number of blank cells when you give the COUNTBLANK() formula a dynamic column reference as opposed to a specific range of cells. I discovered the answer in a Mr. Excel forum thread about this exact issue.

Say you have the following list of students, and you want to count the number of cells with a value, you would simply use the COUNTA() function like so:

This gets you a count of 8 students. You will notice that there are two “blanks” in rows 4 and 7. How would you count the number of blanks in these 10 cells? You could use a COUNTBLANK() function like so:

Easy enough right? This formula gives you the answer of 2. However, sometimes your formula for counting the number of blanks may not be right below your actual range of data, and you may not know the exact cell references do your range. In the below case, the result of the COUNTBLANK() function leads to an answer of 1048568 which is a lot of empty cells!

What’s happening is Excel is counting all the blank cells below row 10 which is not really part of my data set. How would I tell Excel to only count the number of blank cells in my data range (A1:A10) when given an entire column reference?

The entire formula for the above scenario would be the following:

=COUNTBLANK(A1:INDEX(A:A,MATCH(REPT("Z",255),A:A)))

What’s interesting about this formula is that it only requires you to know the “top” of your range which is the “A1” reference. The REPT() function is a new one I haven’t used before, and this function creates the value “ZZZ[…]” in whatever cell you choose (the “Z” letter repeated 255 times). The MATCH(REPT("Z",255),A:A) part of the formula is a common formula to find the row number of the last cell with a value in your data set. Similar to VLOOKUP(), the MATCH() function also has a lookup_type parameter when set to “1” or omitted, will do a fuzzy or approximate lookup on your data set. It tries to find the the largest value that is equal to or less than the value you are trying to match against.

In this case, since REPT("Z", 255) results in a value that you will most likely not have in your data set and is the “last” value you would find if used in a data set, the MATCH() function return the next largest value which is the last cell with text in your range (e.g. cell “A10” in our example above). Why is the “255” significant? Turns out that this was the character limit for cells in Excel ’95 and earlier, and is still the limit for how Excel stores “records” despite the limit being extended to 31,000 starting with Excel ’97.