Countifs is a handy function in Google Sheets that allows you to return the count of an array or range based on multiple conditions. However, what if you want to use the ‘not blank’ condition? In this tutorial, we will explore how to use ‘not blank’ as a condition in the Countifs function.
Not Blank as a Criterion in Conditional Count
To specify ‘not blank’ in Countifs, you can use the <> operator. For example, the following Countifs formula will count all the non-blank cells in the range A1:A10:
=countifs(A1:A10,"<>")But wait, you might be thinking, isn’t the Countif function enough for this? And you’re right! The Countif function does the same thing:
=COUNTIF(A1:A10,"<>")The real power of Countifs lies in its ability to handle multiple criteria. So let’s see how to use ‘not blank’ as one of the conditions in Countifs.
Countifs <> in One Column and Condition from Another Column
Suppose we have a sample dataset like the one shown below:
We want to count all the unfinished tasks in column A. To do this, we can use the following Countifs formula:
=countifs(B2:B,"No",A2:A,"<>")In this formula, we are considering two conditions. First, we check if the ‘Status’ in column B is equal to “No”, indicating an unfinished task. Second, we exclude the blank cells in column A by using the <> operator. This ensures that we only count the “No” values in column B when the corresponding cells in column A are filled.
If you are using tick boxes instead of “Yes” or “No” in column B, the formula would be:
=countifs(B2:B,FALSE,A2:A,"<>")This formula counts all the unchecked tick boxes if the corresponding cells in column A are not blank.
By the way, do you know how to convert “Yes” or “No” to tick boxes in Google Sheets? Let me show you!
Convert “Yes” or “No” to Tick Boxes in Google Sheets
First, select the range containing the “Yes” or “No” values. Then go to the menu Edit > Find and Replace. In the “Find” field, type “Yes” without quotes, and in the “Replace with” field, type TRUE. Click “Replace all”. Repeat the same process for replacing “No” with FALSE.
Once you have replaced the values, select the range where you want the tick boxes to be placed and go to the menu Insert > Tick box. This will quickly replace the “Yes” or “No” values with tick boxes in Google Sheets.
Now, let’s move on to using ‘not blank’ and another condition from the same column in Countifs.
Countifs with <> and One More Condition from the Same Column
When it comes to using ‘not blank’ and another criterion in the same column, we are essentially dealing with OR criteria in Countifs. However, the method for using ‘not blank’ in OR criteria is different from the normal OR use in Countifs.
Here’s an example of using OR criteria in Countifs:
=ArrayFormula(SUM(countifs(A2:A,{"Hold","Approved"})))
This formula counts the values “Hold” and “Approved” in column A and returns the count as an array {3,1}. The SUM function then adds up the values in this array.
However, using <> as a condition in Countifs requires a slightly different approach:
=countifs(A2:A,"<>Hold",A2:A,"<>")
In this Countifs formula, we exclude the string “Hold” as well as blank cells in the count.
These are the formulas you can use when you want to apply the ‘not blank’ condition in Countifs in Google Sheets.
Stay tuned for more useful formulas and tutorials!
Check out my website Crawlan.com for additional resources and tips on using Google Sheets.
Additional Resources:
- Countifs with Not Equal to in Infinite Ranges.
- COUNTIFS in a Time Range in Google Sheets.
