How to Use COUNTBLANK Function in Google Sheets

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.

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.

How to use COUNTBLANK function in Google Sheets

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.

How to use COUNTBLANK function in Google Sheets

Clever, right?

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:

How to use COUNTBLANK function in Google Sheets

With the help of the COUNTBLANK function, he was able to identify the number of exams that haven’t been taken for that week.

How to use COUNTBLANK function in Google Sheets

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:

=COUNTBLANK(range)

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.

How to use COUNTBLANK function 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.

How to use COUNTBLANK function in Google Sheets

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.

How to use COUNTBLANK function in Google Sheets

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

  1. 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.

How to use COUNTBLANK function in Google Sheets

 

  1. 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).

How to use COUNTBLANK function in Google Sheets

 

  1. Type open parenthesis ‘(‘ or simply hit Tab key to let you use that function.

How to use COUNTBLANK function in Google Sheets

 

  1. 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’.

How to use COUNTBLANK function in Google Sheets

 

  1. 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.

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like