The COUNTBLANK function in Google Sheets is useful if you want to get the number of empty cells in a given range.
Meaning, the COUNTBLANK function returns a number that corresponds to the count of empty cells in a range.
Table of Contents
The rules for using the COUNTBLANK function in Google Sheets are as follows:
- Cells that contain text, numbers, errors, etc. are not counted.
- Formulas that return empty text are counted.
- Cells that contain zero are considered not blank and will not be counted.
Let’s take an example.
Jane has been keeping and managing a record of her customers’ payment status of her business. See her table below:
She has a total of 25 customers for April and she wants to get a summarized report with the total number of customers who have already paid and those who haven’t.
For those who already paid, she marked them ‘Paid’ whereas she leaves the Payment Status field blank if not.
Using the COUNTIF function, which she learned from Sheetaki.com, she easily was able to pull the total number of those who already paid.
What she wants to do next is to pull the other group, which are the customers who haven’t settled their payment yet.
While there are many other ways to do it, Jane has been smart to use the COUNTBLANK function to get the number.
Let’s have another example!
Justin, a teacher, wants to get a glimpse of the number of students who have missing exams in a week. See his table below:
With the help of the COUNTBLANK function, he was able to identify the number of exams that haven’t been taken for that week.
Watch out for a more advanced tutorial and examples on how you can use the COUNTBLANK function in the coming weeks. Be sure to subscribe to be notified.
Perfect! Let’s begin getting to know more about our COUNTBLANK function in Google Sheets.
The Anatomy of the COUNTBLANK Function
So the syntax (the way we write) the COUNTBLANK function is as follows:
Let’s dissect this thing and understand what each of these terms means:
- = the equal sign is just how we start any function in Google Sheets. It is how Google Sheets understand that we are asking it to either do computation or use a function.
- COUNTBLANK() this is our COUNTBLANK function. It returns the number of empty cells in a given range
- range is the range of cells in which to count blank cells.
A Real Example of Using COUNTBLANK Function
Let’s take a look at the payment record that Jane created below to see how the COUNTBLANK function is used in Google Sheets.
The COUNTBLANK function is pretty much straightforward. It counts the blank cells in the provided range.
In this case, the range passed to the function is B2:B26, which is column B, from row 2 to 26. This means that the function will count the number of blank cells in cell range B2:B26.
The function returned 10, which corresponds to the number of blank cells in the given range.
You can quickly verify this by filtering the Status column to (Blank) values only.
You should be able to see that 10 payees have blank values in column B.
Jane could have used the method above to count the blank values in column B. However, this would take more manual steps than that of using the COUNTBLANK function.
Now, let’s take a look at Justin’s student record below:
In the example above, the cell range is consists of multiple columns, which are the columns B to F.
This implies that you may also pass a wide cell range for the COUNTBLANK function.
You may make a copy of the spreadsheet using the link I have attached below.
How to Use COUNTBLANK Function in Google Sheets
- Click on any cell to make it the active cell. For this guide, I will be selecting I3, where I want to show the result.
- Next, type the equal sign ‘=‘ to begin the function and then follow it with the name of the function, which is our ‘countblank‘ (or ‘COUNTBLANK‘, not case sensitive like our other functions).
- Type open parenthesis ‘(‘ or simply hit Tab key to let you use that function.
- Now the exciting part! Let’s give our function its only argument, the range. Click the cell range or column that you want to use. In this case, click on cell B2 and drag it until cell F13. Alternatively, you may type in ‘B2:F13’.
- Finally, hit your Enter or Tab key. Cell I3 will now show you the return value of the COUNTBLANK function, or the number of blank cells in the range B2:F13.
That’s pretty much it. You can now use the COUNTBLANK function in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.