You may already be familiar with using the Countifs function with the “not equal to” operator (<>) in Google Sheets. It works similarly to the NOT function. But do you know how to use Countifs with “not equal to” in an infinite range using the <> operator?
In this new Google Sheets tutorial, I’ll provide answers to the following Countifs-related questions:
The Function Countifs – How to
If you have time, check out my comprehensive guide on the Count function. It covers all the Count-related functions in Google Sheets, including Countifs.
But for those who are busy and don’t have spare time, here’s a formula that explains the use of Countifs.
The Countifs formula counts the name “Apple” in Column A if the corresponding value is “Passed” in Column B. In other words, it counts if all the criteria in the provided data ranges are matching.
If you want to learn more about Countifs, you can follow these formula examples:
- Countifs with Multiple Criteria in the Same Range in Google Sheets.
- How to Find Multiple Occurrences of Rows in Google Sheets [Duplicates].
- Countifs Array Formula in Google Sheets.
How to Use “Not Equal to” in Countifs in Google Sheets
Our topic is using the Countifs function with “not equal to” in infinite ranges. Let’s first see the formula in fixed or finite ranges.
With the above Countifs formula, I planned to count the values in Column A if the name is “Apple” and the 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, such as A2:A7 and B2:B7.
Now, here comes the issue. I want to use the <> operator in Countifs with infinite ranges like A2:A and B2:B. What’s the problem?
How to Use Countifs with “Not Equal to” in Infinite Ranges
Let’s see the example just above. I am using the same formula with infinite ranges.
=countifs(A2:A,"Apple",B2:B,"<>Failed")
I don’t see any issue. This formula also returns the correct result. You may have a question, right?
But here’s 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”.
In a 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 to avoid blank rows at the end of the range. But how do you exclude blank rows in Countifs when using infinite ranges as mentioned above? Here’s the proper solution.
Replace the range A2:A with the following Query.
=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 follows.
=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.
Click here to learn more about Google Sheets and boost your productivity with Crawlan.com
