Do you want to use Countif in an array in Google Sheets to get expanded results? Look no further, because I have the perfect solution for you! Forget about repeating counts in each row – I’ll show you how to use a simple Query formula for a clean and efficient alternative. In this tutorial, I’ll provide you with different Countif alternative solutions and teach you how to combine and nest different functions. Get ready to level up your Google Sheets game!
Different Methods for Getting Countif Array Results
-
Query (Formula #2): The Query formula provides a clean-looking Countif array result. You’ll get the unique values of the selected column and their count as the second column in a new range.
-
Unique + ArrayFormula + Countif Combination (Formula #5): You can achieve the Query output mentioned in point #1 by using a Unique + ArrayFormula + Countif combination. This powerful formula combination will give you the same result as the Query formula.
-
Countif with ArrayFormula (Formula #4): If you want to get an expanding result, you can use the Countif function with the ArrayFormula. This solution returns the count of the selected column values against each row. For example, if the value “apple” repeats in rows 5, 7, and 8, the formula will put the count 3 in rows 5, 7, and 8.
-
Array Formula + Vlookup + Query Combination (Formula #3): The formula mentioned in point #3 can be replaced by an Array Formula, Vlookup, and Query combination. This combination will give you the same result with a different approach.
I know it may seem a bit messy, but don’t worry! I’ve arranged the formulas in a particular order below to make it easy for you to follow along. Let’s dive in!
The below examples will help you understand the problem with a normal Countif in an array, how a Query handles Countif, and why my combo formula is required.
How to Use Countif in an Array in Google Sheets
Let’s start with a real-life example. Imagine you have a list that shows the names of the Wimbledon Men’s Singles Champions from 2000 to 2017. You can use the COUNTIF formula to find the number of titles won by each player. For example, with the help of the COUNTIF formula in cell F2, you can find the number of titles won by the player in cell E2.
Formula #1: =countif(C2:C19,E2)
But what if you want to find the number of titles won by each gentleman in Column C? Can you use Countif in that way? Of course, you can! This solution can be found under Formula #4. However, the easiest and most clean-looking solution is to use a Query.
You can use the following Query formula as an alternative to Countif in an Array in Google Sheets:
Formula #2: =query(C2:C19,"select C, count(C) group by C label count(C)''")
This Query formula will give you the same count array result as mentioned in Formula #1 but in a clean and organized manner. You can even get the same result by using a Unique + ArrayFormula + Countif combo, which will be covered in Formula #5.
Vlookup a Query Result to Get Countif Array Result in Google Sheets
Now let’s take it a step further and use a Vlookup and Query combo. We want to get the number of titles won by each player against their own name. We can achieve this by using the Query formula mentioned above in a Vlookup function.
Formula #3: =ArrayFormula(vlookup(C2:C19,query(C2:C19,"select C, count(C) group by C label Count(C)''"),2,FALSE))
In Formula #3, the Query formula (Formula #2) is used as the range in the Vlookup function. This allows us to search for the winners’ names (search key) in the first column of the Query result and return the corresponding count (number of titles) from the second column. This combination is essential to learn, as you can replace “count” with other aggregation functions in Query and use them in Vlookup for different types of array results.
Countif Multiple Criteria Output Using the Range Twice as Range and Criteria
Believe it or not, we can replicate the result of Formula #3 using Countif itself! Apply this formula in Cell D2 to get the same result as the Query and Vlookup output. It’s incredibly simple!
Formula #4: =ArrayFormula(countif(C2:C19,C2:C19))
You might be wondering why this formula comes later in the order if it produces the same result as Formula #3. Well, I wanted to show you how we can use the Query result (Formula #2) in Vlookup as a range. Additionally, this formula can also return the same result produced by Formula #2 if you wrap it with the UNIQUE function.
Formula #5: =ArrayFormula(unique({C2:C19,countif(C2:C19,C2:C19)}))
Isn’t it amazing how much you can achieve with just a few formulas? Now you’re equipped with five powerful formulas that can help you master Countif in an Array in Google Sheets. If you find any part of this tutorial difficult to understand, don’t hesitate to contact me via the comment form below. I’m here to help!
Remember, you can always find more helpful tutorials and resources on Crawlan.com. Happy counting!
Related:
