Google Sheets Countifs can return the count of an array/range depending on multiple conditions. What about one condition in that is ‘not blank’? Here is a quick tutorial on how to use not blank as a condition in the Countifs function in Google Sheets.
In short, in this Spreadsheet tutorial, I am going to explain/pay attention to the following points.
- Countifs function with not blank as the criterion/condition in one column and one more criterion from another column.
- Not blank as a condition in one column and one more condition from the same column.
This tutorial is revolving around the above two points and I will try to make it short and to the point. So here we go.
Not Blank as a Criterion in Conditional Count
To specify not blank in Countifs (conditional count) use the <>
operator. The below Countifs formula would count all the non-blank cells in the array A1:A10.
=countifs(A1:A10,"<>")
This way you can use not blank as a condition in the Google Sheets Countifs function. But for this, no need to use Countifs, right? The function Countif will do the same thing.
=COUNTIF(A1:A10,"<>")
The real role of Countifs is multiple criteria count. Let’s see how to use not blank as one of the conditions in Countifs in Google Sheets.
First, I am going to use not blank as a criterion in one column and one more criterion from a different column.
Countifs “<>” in One Column and Condition from Another Column
Let’s apply the Countifs formula in the below sample dataset. This will be easy for me to refer/explain the conditions.
=countifs(B2:B,"No",A2:A,"<>")
Explanation:
The purpose of this Countifs formula is to count all the unfinished tasks in column A.
The “Status” (Column B) is equal to “No” which means the task is not finished. But some of the statuses are not yet filled in column A. So I don’t want to count all the “No” in column B.
I just want to count the values “No” in “Status” column B if the corresponding cells in column A are filled. That is what the above Countifs formula does.
If you are using tick boxes instead of “Yes” or “No” in column B, then the formula will be as follows.
=countifs(B2:B,FALSE,A2:A,"<>")
The above Countifs formula counts all the unchecked tick boxes if the corresponding cells in column A are not blank. Here is a bonus tip!
Do you know how to convert “Yes” or “No” to Tick Boxes in Google Sheets?
Convert “Yes” or “No” to Tick Boxes in Google Sheets
Here let’s take the dataset used in the first example. I am going to convert the “Yes” or “No” to tick boxes.
First, you must replace the values “Yes” to TRUE and “No” to FALSE. For that, select the range B2:B and go to the menu Edit > Find and Replace.
In the “Find” field type “Yes” without quotes and in the “Replace with” field type TRUE. Select “Specific range” against the “Search” field and hit “Replace all”.
Similarly, replace “No” with FALSE. Then select the range B2:B11 or up to the row in which you want the tick boxes to be placed. Go to the menu Insert > Tick box.
This way we can quickly replace “Yes” or “No” with TRUE or FALSE tick boxes in Google Sheets.
Countifs with “<>” and One More Condition from the Same Column
Countifs function with not blank and one more criterion in the same column means there are two criteria in the same column. That means OR criteria in Countifs.
The method of using not blank in OR criteria in Countifs is different from the normal OR use in Countifs.
I have already a Sheets tutorial on how to use OR criteria in Countifs. You can find that here – Countifs with Multiple Criteria in the Same Range in Google Sheets.
It’s like this.
=ArrayFormula(SUM(countifs(A2:A,{"Hold","Approved"})))
Here I have used two conditions in column A in Countifs. The =ArrayFormula(countifs(A2:A,{"Hold","Approved"}))
will count the value “Hold” and “Approved” and return the count as an array {3,1}
.
The Sum in between will sum the values in this returned array. That method won’t work well when using “<>” as a condition in Countifs in Google Sheets. There is a quite simple and straightforward approach.
=countifs(A2:A,"<>Hold",A2:A,"<>")
This Countifs formula will exclude the string “Hold” as well as blank cells in the count.
You can follow the above formulas when you want to use not blank as a condition in Countifs in Google Sheets.
Additional Resources: