What Is COUNTBLANK Function In Google Sheets?
The COUNTBLANK in Google Sheets helps users exclusively counts only the cells that are blank or empty ignoring the non-blank cells.
Users can utilize the COUNTBLANK function in Google Sheets to find the count of the financial and accounting statements, based on blank cells to avoid missing any information.
For example, the dataset below contains a few items purchased and their quantities. We will find the count of the missing items or blank cells using the Google Sheets COUNTBLANK function.
Select cell B9, enter the formula =COUNTBLANK(A1:B7) and press “Enter”, as shown below.
We can see that the count of blank cells is 2, in the list of 6 items in the list.
Key Takeaways
- The COUNTBLANK in Google Sheets is a math function that counts the blank, empty or cell values without any data.
- It does not consider cells containing numeric values, texts, logical values, error values, zero, or space characters as empty cells.
- The COUNTBLANK function is from the group of COUNT functions that also has COUNTBLANKS, COUNTA, COUNTIF, COUNTIFS, COUNTUNIQUE, COUNTUNIQUEIFS, that returns the count of all the required values that satisfy the specific criteria. Therefore, ensure to enter the right function name, to get the right formula results.
- We can use the COUNTBLANK functions with other function, such as IF(), SUM(), etc.
- As an alternate option, we can using the Conditional Formatting method to highlight the blank cells, and count the colored blank cells as well.
Syntax
The syntax of the COUNTBLANK formula in google sheets is,
The arguments of the COUNTBLANK formula in google sheets are,
- value1: It is the selected cell range from the dataset to find the count of blank cells. It is a mandatory argument.
- [value2, …]: It is an additional cell range to find the count of blank cells. It is an optional argument.
How To Use COUNTBLANK In Google Sheets?
We can use the Google Sheets COUNTBLANK in the following two methods, namely,
- Access from the ribbon.
- Enter into the worksheet manually.
Method #1 – Access From the Ribbon 🡪
Step 1: Choose an empty cell for output 🡪 select the “Insert” tab 🡪 click the “Function” option right arrow 🡪 click the “Math” option right arrow 🡪 select the “COUNTBLANK” function, as shown below.
The COUNTBLANK formula appears as shown below.
Step 2: Select the range argument as a cell rangeand press “Enter”.
Method #2 – Enter in the Worksheet Manually 🡪
Step 1: Select an empty cell to display the output.
Step 2: Type =COUNTBLANK( in the chosen cell. [Alternatively, enter =C or =COUNTB and click the COUNTBLANK function from the suggestions Google Sheets lists.]
Step 3: Enter the cell range as the argument value.
Step 4: Close the brackets and press Enter to obtain the count of the blank cells.
Examples
We will consider some COUNTBLANK in Google Sheets examples to understand the function better.
Example #1 – Count Inventory Items with Missing Stocks.
The following dataset contains a list of electronic inventories and their items sold. We will count inventory items with missing stocks using the COUNTBLANK() function to find the count of blank cells where the units sold details are not found.
The procedure to count the blank cells using the COUNTBLANK is,
Select cell D2, enter the formula =COUNTBLANK(A1:B7) and press “Enter”, as shown below.
The function looks for the “blank” cell values in the range A1:B7 and returns the value of 3. Remember we have selected the entire dataset from A1:D7 that consists of column headers that are text, numeric values, text values, etc. And the formula has ignored the non-blank cells an returned the rest of the blank cells because that is where the details or the inventory items data is missing.
Example #2 – Count Students Who Didn’t Submit Assignments.
Consider the table given below that consists of 15 students’ assignment submission data. We will Count students who didn’t submit assignments using the COUNTBLANK in google sheets.
We have entered an IF() formula in column E, where once the students submit the assignment on the last date and the date is entered, then, the result cell, i.e., corresponding column E cell, will populate.
The procedure to count students who didn’t submit assignments are,
Select cell H1, enter the formula =COUNTBLANK(E2:E16) and press “Enter”, as shown below.
The function looks for the blank cell values in the range E2:E16, that indicates that the assignment is not submitted. As the student submits and the submission date is updates against the student’s name, automatically the COUNTBLANK formula cell also gets updated.
Example #3 – COUNTBLANK with IF.
The dataset given below is a firm’s monthly sales generated for the electronic items sold and the total of the sales in column C. We will use COUNTBLANK with IF to get an alternate result other than the count of the blank cells.
The procedure to use the COUNTBLANK and the IF functionsis,
Select cell D1, enter the formula =IF(COUNTBLANK(C2:C13)>0,”Blank Cells”,”No Blank cells”) and press “Enter”, as shown below.
The function looks for the “blank not sold” cell values in the range C2:C13 and returns “Blank Cells”, indicating that the cell range consists of blank cells. Once all the cell values are added, then the formula will update to No Blank Cells, according to the set condition in the formula.
Example #4 – COUNTBLANK with SUM.
The dataset below displays a student’s final exam scores and the scores awarded for practical’s and reading. We will use the COUNTBLANK with SUM to calculate the sum if all the details are present.
The steps to use COUNTBLANK with SUM are,
Step 1: Select cell E1 and enter the formula =IF(COUNTBLANK(C2:D2)=0,SUM(C2:D2),”Blank”), as shown below.
Step 2: Press “Enter” and Google Sheets will provide the autofill option, as shown below. We can either select it or use the fill handle method.
Step 3: Drag theformula from cell E2:E9 using the fill handle, to get the following results.
The function looks for the mark’s cells in the range of each row, if all the data is available, then the formula returns the sum of the marks, or else it returns as Blank, indicating that there is missing data.
Important Things To Note
- If we supply the values directly then it must be entered within double quotations. Otherwise, the formula will not work.
- An empty or a blank cell will still be ignored as a blank cell, if it has some white spaces in it. And also, the function counts cells containing formulas that return an empty string (“”) as the output.
- Ensure to provide the arguments, because if we do not provide inputs, then we will get the “#NA” error.
- Since we have many functions in the COUNT group, we must choose the right function, especially while entering the formula manually. If not, we will get a “#NAME?” error.
Frequently Asked Questions (FAQs)
There are times when we cannot remember in which category the “COUNTBLANK” function falls. Then, we can insert the function as follows:
Choose an empty cell 🡪 select the “Insert” tab 🡪 click the “Function” option right arrow 🡪 click the “All” option right arrow 🡪 select the “COUNTBLANK” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion. We can use the same path to insert other count functions, such as, COUNTBLANKS, COUNTA, COUNTIF, COUNTIFS, COUNTUNIQUE, COUNTUNIQUEIFS, as they are just above and below the COUNTBLANK function, as seen in the above image.
A few reasons the Google Sheets COUNTBLANK may not work are,
a. The range values selected does not have blank or empty cells.
b. We have entered the direct values without double-quotes.
c. The argument range is not entered.
d. The dataset is update or modified, i.e., the previously blank cell values, are updated with values.
The difference between COUNT non Blank and COUNTBLANK is that,
● The COUNTBLANK function exclusively counts the blank cells and ignore the non-blank cells.
● On the other hand, the COUNT not blank formula we use the COUNT function along with the operator “<>” and this will exclusively counts the non-blank cells.
Alternatively, we can find the Functions icon to insert the Google Sheets COUNTBLANK by following the path shown below.
● Choose an empty cell 🡪 click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
● A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option 🡪 click the “All” option right arrow 🡪 select the “COUNTBLANK” function, as shown below.
Download Template
This article must help understand COUNTBLANK in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is COUNTBLANK In Google Sheets. We learn syntax & how to use COUNTBLANK in Google Sheets, examples, working template. You can learn more from the following articles:
Leave a Reply