You may already know how to use the function Countifs with not equal to in Google Sheets. I am talking about the<> operator that works similarly to the NOT function. But do you know how to use Countifs with not equal to in an infinite range with the said operator?

In this new Google Sheets tutorial, you can get answers to your below Countifs-related questions.

1. The function Countifs – How to?
2. How to use not equal to in Countifs in Google Sheets?
3. How to use Countifs with not equal to in infinite Ranges?

Let’s begin!

The Function Countifs – How to

If you have time, check my Count function guide. That is one single tutorial that covers all the Count related functions in Google Sheets. In that, you can find the Countifs too.

But for those who are busy and have no spare time to check that, here is the formula that explains the use of Countifs.

The Countifs formula that I’ve used in the above example counts the Name “Apple” in Column A if the corresponding value is “Passed” in Column B.

In concise the Countifs formula counts if all the criteria in the provided data ranges are matching.

If you want to learn more about Countifs, other than the above-mentioned Count Functions guide, you can follow these formula examples.

Countifs Advanced Formula Examples:

1. Countifs with Multiple Criteria in the Same Range in Google Sheets.
2. How to Find Multiple Occurrences of Rows in Google Sheets [Duplicates].
3. Countifs Array Formula in Google Sheets.

How to Use Not Equal to in Countifs in Google Sheets

The use of the function Countifs with not equal to in infinite ranges is our topic. First, see the formula in fixed or finite ranges.

With the above Countifs formula what I planned to do is count the values in Column A if the Name is “Apple” and Quality is not failed.

In Countifs with not equal to criteria, you can use the<> operator, not the NE operator or the NOT logical function.

In the above example, the range is fixed. I mean you can see the range as A2:A7 and B2:B7.

I want to use the NOT operator in Countifs in infinite ranges like A2:A, and B2:B. What is the issue then?

How to use Countifs with Not Equal to in Infinite Ranges

See the just above example. I am using the same formula with infinite ranges.

=countifs(A2:A,"Apple",B2:B,"<>Failed")

I can’t see any issue. This formula is also returning the correct result. You may want to ask this question, right?

Then there is the real problem. I want to use the<> operator in the two criterion ranges. I mean I want to count the Name in Column A as not equal to “Orange” and Column B as not equal to “Failed”

Multiple Not Equal to in Countifs in Google Sheets

In fixed or finite range it’s easy. The below formula will only count rows in the range that don’t contain the values “Apple” or “Failed”.

=countifs(A2:A7,"<>Apple",B2:B7,"<>Failed")

But the infinite range formula as below would return a wrong output as it counts all the blank rows too.

=countifs(A2:A,"<>Apple",B2:B,"<>Failed")

So the key here is avoiding blank rows at the end of the range. Then how to exclude blank rows in Countifs when you use infinite ranges as above? Here is the proper solution.

Replace the range A2:A with the Query below.

=query(A2:B,"Select A where A<>''")

Replace the range B2:B with the following Query.

=query(A2:B,"Select B where A<>''")

So the formula would be as below.

=countifs(query(A2:B,"Select A where A<>''"),"<>Apple",query(A2:B,"Select B where A<>''"),"<>Failed")

You can replace the above Countifs + Query combo with the Query itself. Here is that formula.

=query(A2:B,"Select Count(A) where A<>'Apple' and B<>'Failed' label Count(A)''")

That’s all. You can use this method when you want to use Countifs with Not Equal to in Infinite Ranges.