Google Sheets is a powerful web-based spreadsheet app that enables you to analyze and organize data in different cells. These cells can house a variety of data types, and of course, they can also be blank as they are most of the time.
One important operation when using Google Sheets in statistics is getting a count of specific cells, and one such specificity is getting a count of cells that don't have any data in them at all. Luckily, Google Sheets has a function that is intended exactly for that purpose.
The COUNTBLANK Function in Google Sheets
COUNTBLANK is a Google Sheets function that counts blank cells in a specific range and then outputs the resultant number. The syntax for this function is as below:
=COUNTBLANK(range)
As you can see for yourself, COUNTBLANK is a straightforward function that takes in a single parameter, which is the range. Once you feed the range to COUNTBLANK, it will look through the range and return the number of cells that don't contain any values whatsoever.
It's important to understand what cell exactly merits as a blank cell before you get down to using COUNTBLANK in Google Sheets. Frankly, COUNTBLANK doesn't care about the looks of a cell. To qualify as a blank cell, the cell must be absolutely blank:
- Cells that contain formulas which return a blank value are not considered blank.
- Cells that contain values but look blank through custom number formats in Google Sheets are not considered blank.
- Cells that contain blank text such as =" " are not considered blank.
- Cells that return error values are not considered blank.
All in all, a cell must be really empty for COUNTBLANK to count it. With that in mind, let's count some blank cells in Google Sheets with COUNTBLANK.
How to Count Blank Cells With the COUNTBLANK Function in Google Sheets
As you already know by now, COUNTBLANK is a simple function. You feed it a range of cells, and it returns the number of blank cells. Yet, despite its ostensible simplicity, you can couple COUNTBLANK with other functions in Google Sheets to create impressive formulas.
To get started, we're going to use COUNTBLANK to simply count the number of blank cells in a range. Afterward, we're going to combine COUNTBLANK with two other Google Sheets functions to demonstrate how useful this faithful function is.
Using COUNTBLANK in Google Sheets: A Simple Example
In this sample spreadsheet, we have the names of ten volunteers for a study along with their scores in a related examination. Some volunteers failed to partake in the examination, and therefore they don't have a score next to their names.
The goal here is to count the number of volunteers who didn't partake in the examination. Since you're familiar with COUNTBLANK, the path ahead should be clear. We're going to use COUNTBLANK to count the number of blank cells in the second row, as this is equivalent to the number of volunteers who didn't partake in the examination.
- Select the cell where you want to display the results. In this example, we're going to use cell E1.
-
In the formula bar, enter the formula below:
=COUNTBLANK(B2:B11) - Press Enter.
COUNTBLANK will now give you the count of volunteers who didn't partake in the examination. COUNTBLANK is an ideal function in scenarios like this, where the number of blank cells is the desired end product. But that's not all that there is to COUNTBLANK.
Using COUNTBLANK in Google Sheets: A Compound Example
Like almost any other Google Sheets function, you can combine COUNTBLANK with other functions to perform operations that the same functions couldn't perform alone. There are virtually infinite ways to harness the synergy of compound formulas in Google Sheets. One such example, although a humble one, lies below.
Suppose that in the same study as the previous example, we are told that if more than half of the volunteers partook in the examination, then a second examination should take place.
In this example, our task is to write up a formula that determines whether a second examination should take place or not. To achieve this, we're going to use COUNTBLANK, COUNTA, and IF all in one formula.
=COUNTBLANK(range)
You're already familiar with this one! We're going to use COUNTBLANK to get the number of blank cells, i.e volunteers who didn't partake in the examination.
=COUNTA(value1, value2, ...)
This function is essentially the opposite of COUNTBLANK, as COUNTA counts the non-blank cells in a range. We're going to use COUNTA to get the count of non-blank cells, i.e. volunteers who did partake in the examination.
=IF(logical_expression, value_if_true, value_if_false)
This is perhaps the most used function in compound formulas. IF takes in a logical expression, and returns one of two values depending on whether the expression is true or false. It's best if you read our article on how to use the IF function in Google Sheets before going forward with this guide.
With these three functions in mind, the formula that we need is already taking shape. To determine whether more than half of the volunteers participated in the examination, we need to check whether the number of those who did participate in the examination (COUNTA) is larger than those who didn't (COUNTBLANK).
If this expression is true, then a second examination should take place. If it's false, then a second examination shouldn't take place (IF). Let's see these functions at work:
- Select the cell where you want to display the output of your formula. In this example, we're going to use cell E5.
-
In the formula bar, enter the formula below:
=IF(COUNTA(B2:B11)>COUNTBLANK(B2:B11), "A second exam should take place.", "A second exam shouldn't take place.") - Press Enter.
The formula we used here summons the IF function and feeds it a logical expression. The logical expression is whether the number of non-blank cells is greater than the number of blank cells in the range B2:B11.
Next, we indicate that should this expression be true, the formula will output A second exam should take place, and if it is false then it will output A second exam shouldn't take place.
Once you press Enter and finalize the formula, the cell will show one of the two scripts that we fed the formula.
In our case, since six of the ten volunteers had partaken in the first examination, the formula returned A second exam should take place. However, if we were to delete two of the scores from the list, the formula would change its mind.
Leave the Headcount to Google Sheets
Google Sheets is a powerful data analysis and manipulation tool. Not only does Google Sheets come packed with an arsenal of functions, it also provides you with the ability to combine these functions and create unique formulas. COUNTBLANK is a simple Google Sheets function that serves a sole purpose: to count blank cells.
In some scenarios, the number of blank cells in a range might just be what you need. In some other scenarios, you might need to feed the number of blank cells in a range to yet another function. Whichever it is, you'll find COUNTBLANK a reliable function that excels at its purpose.