HomeGoogle DocsSpreadsheetNot Blank as a Condition in Countifs in Google Sheets

Not Blank as a Condition in Countifs in Google Sheets

Published on

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.

  1. Countifs function with not blank as the criterion/condition in one column and one more criterion from another column.
  2. 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.

Example to Not Blank as a Condition in Countifs
=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,"<>")
Countifs - Unchecked tick boxes in one column and not blank in another column

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

Convert "Yes" or "No" to Tick Boxes in Google Sheets

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"})))
Countifs OR Use in Docs Sheets

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:

  1. Countifs with Not Equal to in Infinite Ranges.
  2. COUNTIFS in a Time Range in Google Sheets.
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here