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.
Top comments (0)