Cara menggunakan google sheet match array

You can use Countif in an array in Google Sheets to return an expanded result. But it will repeat the count in each row. The best and clean looking alternative solution is using a simple Query formula.

Here in this tutorial, I am going to provide you different Countif/Countif alternative solutions. This can also help you to understand how to combine/nest different functions.

Different Methods for Getting Countif Array Result

1. Query (Formula # 2 below): It provides a Countif array result which is clean looking. You will get the unique values of the selected column and its count as the second column in a new range.
2. The Query output mentioned in point # 1 can be achieved by using a Unique + ArrayFormula + Countif combination (Formula # 5 below).
3. The use of Countif function with the ArrayFormula to get an expanding result (Formula # 4 below). This solution returns the count of the selected column values against it in each row. For example, if the value “apple” repeats in row 5, 7, and 8 the formula will put the count 3 in row 5, 7 and 8.
4. The formula in the above point # 3 can be replaced by an Array Formula, Vlookup and Query combination (Formula # 3 below).

Lots of messy stuff, right? But keep cool as it’s so simple with examples. I have arranged the formulas in a particular order below. I hope you will find that easy to follow.

The below examples will explain itself what is 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

I have a list which shows the name of the Wimbledon Men’s Singles Champions from 2000 to 2017.

With the help of a COUNTIF formula as in cell F2, I can find the number of titles won by the player in E2.

Formula # 1:

=countif(C2:C19,E2)

Then how to find the number of titles won by each gentleman in Column C? Can I use Countif that way?

Of course, you can. That solution you can find under the Formula # 4. But the easiest and clean looking solution is using a Query.

You can use a Query formula as below as an alternative to Countif in an Array in Google Sheets.

Similar: Expand Count Results using MMULT in Google Sheets

Query for Clean Looking Countif Array Result

In cell E2 you can see my Query formula.

Formula # 2:

=query(C2:C19,"select C, count(C) group by C label count(C)''")

You can get the same above count array result by using a Unique + ArrayFormula + Countif combo. I’ll come to that later under Formula # 5.

Vlookup a Query Result to Get Countif Array Result in Google Sheets

Now we are going to use a Vlookup and Query combo. What I want to achieve is something like below.

I want the number of titles won by each player against their name itself. We can use the above Query in Vlookup to get the count in each row.

Formula # 3:

=ArrayFormula(vlookup(C2:C19,query(C2:C19,"select C, count(C) group by C label Count(C)''"),2,FALSE))

Formula Explanation:

In this, I’ve used the same Query formula which you can find under Formula # 2.

Now to make you clear about the rest of the formula part, please have a look at the Vlookup syntax below.

VLOOKUP(SEARCH_KEY, RANGE, INDEX, [IS_SORTED])

Here the argument “range” is the above Query Formula # 2 which returns the name of the winners (first column) and their number of titles (second column).

The search key in Vlookup is in C2:C19. The Vlookup will searches down these keys in the first column (name of the winners) in the Query result. Then the Vlookup will return the count/number of titles available in the second column of the Query result.

This combination is a must to learn as you can replace count with other aggregation functions in Query and use in Vlookup for different types of array results.

Countif Multiple Criteria Output Using the Range Twice as Range and Criteria

Formula # 4:

You can replicate the Formula # 3 result using Countif itself! Apply this formula in Cell D2 to get the same above Query and Vlookup output. It’s so simple.

=ArrayFormula(countif(C2:C19,C2:C19))

I know this formula is far better than the Formula # 3. Then why this formula comes later in the order? Because I want to show you how the Formula # 2 result (Query) we can use in Vlookup as a range.

Secondly, and to your surprise, we can use this Formula # 4 to return the same result produced by the Query Formula # 2!

For that you only need to wrap the above formula with the UNIQUE function.

Formula # 5:

=ArrayFormula(unique({C2:C19,countif(C2:C19,C2:C19)}))

Conclusion

I have provided you five formulas and all are entirely different types to help you to get Countif in an Array in Google Sheets.

If you find it difficult to understand, apply all the above steps in your own Google Sheets. If any part of this tutorial seems tough for you, please don’t hesitate to contact me via the comment form below. Enjoy!

Apa yang dimaksud dengan array formula?

Apakah di spreadsheet bisa menggunakan rumus?

Apa saja rumus spreadsheet?

Apa yg diterapkan bagi pemilik Google sheet?