Using ‘Not Blank’ as a Condition in COUNTIFS in Google Sheets

Published on

Google Sheets’ COUNTIFS function can return the count of an array or range based on multiple conditions. But what if one of those conditions is ‘not blank’? This quick tutorial will show you how to use ‘not blank’ as a condition in the COUNTIFS function in Google Sheets.

In this tutorial, I’ll focus on two key scenarios:

  1. Using ‘not blank’ as a criterion in one column while applying another criterion from a different column.
  2. Applying ‘not blank’ as a condition in one column and an additional condition from the same column.

This tutorial is centered on these two points, and I’ll keep it concise and straightforward. Let’s dive in!

Using ‘Not Blank’ as a Criterion in Conditional Counting

Use the <> operator to specify ‘not blank’ in a COUNTIFS (conditional count). The following COUNTIFS formula will count all the non-blank cells in the range A1:A10:

=COUNTIFS(A1:A10, "<>")

However, you don’t need to use COUNTIFS for this. The COUNTIF function can achieve the same result:

=COUNTIF(A1:A10, "<>")

The true purpose of COUNTIFS is to count based on multiple criteria. Let’s explore how to use ‘not blank’ as one of the conditions in COUNTIFS in Google Sheets.

COUNTIFS with ‘<>’ in One Column and Another Condition in a Different Column

Let’s apply the COUNTIFS formula to the sample dataset below, which contains task names in column A and their status in column B. This will make it easier for me to explain the conditions.

=COUNTIFS(B2:B, "No", A2:A, "<>")
Example of Using 'Not Blank' as a Condition in COUNTIFS

Explanation:

The purpose of this formula is to count all the unfinished tasks in column A.

The “Status” (column B) is equal to “No,” meaning the task is not finished. However, some task names in column A are still blank. I don’t want to count every “No” in column B—only those where the corresponding cells in column A are filled. That’s what the above COUNTIFS formula does.

If you are using tick boxes instead of “Yes” or “No” in column B, the formula would be as follows:

=COUNTIFS(B2:B, FALSE, A2:A, "<>")
COUNTIFS: Unchecked Tick Boxes in One Column and Not Blank in Another Column

This COUNTIFS formula counts all the unchecked tick boxes where the corresponding cells in column A are not blank. Here’s a bonus tip!

Do you know how to convert “Yes” or “No” to tick boxes in Google Sheets? Before we move on to the next example of using ‘not blank’ as a condition in COUNTIFS, let’s take a look at this.

Convert ‘Yes’ or ‘No’ to Tick Boxes in Google Sheets

Let’s use the dataset from the first example. I’ll show you how to convert the “Yes” or “No” values to tick boxes.

First, you must replace the value “Yes” with TRUE and “No” with FALSE. To do this, select the range B2:B, then go to Edit > Find and Replace.

In the “Find” field, type “Yes” (without quotes), and in the “Replace with” field, type TRUE. Choose “Specific range” in the “Search” field, then click “Replace all.”

Converting 'Yes' or 'No' to Tick Boxes in Google Sheets

Similarly, replace “No” with FALSE.

Next, select the range B2:B11 or up to the row where you want the tick boxes to appear. Go to the menu Insert > Tick box.

This is a quick way to replace “Yes” or “No” with TRUE or FALSE tick boxes in Google Sheets.

COUNTIFS with ‘<>’ and an Additional Condition from the Same Column

The COUNTIFS function with a ‘not blank’ condition and an additional criterion in the same column means applying two or more criteria to the same column.

This is often needed when you want to count all values in a column except blanks or specific selected values.

The following formula counts all non-blank cells in column A (range A2:A) except those with “Approved” or “Passed”:

=COUNTIFS(A2:A, "<>", A2:A, "<>Approved", A2:A, "<>Passed")

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.