Ever felt the need to count blank cells in a given data and don’t know how? Google Sheets provides us with COUNTBLANK Function to do this task.
This is a super-easy function to master and is very handy at times. In this tutorial we’ll go through different aspects of COUNTBLANK Function and how to use it.
Provides count of blank/empty cell in a given cell range.
Syntax and Parameter Definition
= COUNTBLANK(value1, [value2, value3, .....])
- value1: Can be a constant value(3,2,4 etc), cell reference(A2,B3 etc) or range(B1:B10) which will be considered for counting purpose.
Rest of the arguments have the same definition as of value1, however, they are optional to supply
COUNTBLANK Function counts cells that are blank/empty cells.
It’s important to highlight here that certain types of values listed below, might look empty but are not. Hence, they will not be counted.
- Empty Strings like “”
- String with just spaces like ” “
- Cells containing an apostrophe(‘)
Note: To count cells non-blank cells consider using COUNTA Function
To this point you might have understood the basics of COUNTBLANK. Now lets have a deep dive into examples to get a full picture.
COUNTBLANK Function – Basic Example
In the below example, we have selected different type of data elements and applied COUNTBLANK Function over it.
The output is 2, which is very much expected too.
COUNTBLANK Function – Different type of Blank/Empty Cells
This is a specific nuance which we briefly mentioned earlier too. The idea behind this is that not all cells which look blank will be counted.
This is very well demonstrated by the below example.
As you can see, all of them “look empty”. Empty Strings(C4) are counted but string with spaces(C5) aren’t.
Different Combinations of Input Types
We can use different combination of cell references , ranges , static values as an input to COUNTBLANK. It will return sum total of all blank cells across all the provided inputs.
Note: In Example 3, B6 was supplied twice, once within the cell range, B3:B11 and in second parameter too. Hence, the total count is 3 instead of 2.
Visual Demo of COUNTA Function
Before we end here is a sample visual demonstration of COUNTA Function in action.
That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂