You cannot use Countif in an array in Google Sheets to return expanded results. The best and clean looking alternative solution is using a simple Query formula. But here what I am going to detail to you is something different. I’m using an Array Formula, Vlookup and Query combination for expanded Countif results. The below example will explain itself what is the problem with a normal Countif in an array, how a Query handle Countif and why my Combo formula is required. A lots of messy stuff, right? But keep cool as it’s so simple with examples.
Before going to the tutorial, I suggest you to go through my following tutorial to get a basic idea about the functions in use here.
How to Use Countif in an Array in Google Sheets
I’ve a list which shows the name of the Wimbledon Men’s Singles Champions from 2000 – 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. How to find the number of titles won by each gentlemen’s in Column C? You can’t use Countif in an Array. So you can use Query formula below as an alternative to Countif in an Array in Google Sheets.
I am not going to limit it here. As I’ve told you 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.
Formula and Explanation:
=ArrayFormula(vlookup(C2:C19,query(C2:C19,”select C, count(C) group by C label Count(C)””),2,FALSE))
In this the red coloured part is the same Query formula which I’ve used in above 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 range is the above Query formula 2 result where we have the name of the winners and their number of titles. There are two columns in that result. The first one contain the names and normally it would become the look up range in the Vlookup. The search key is in C2:C19. The Vlookup would lookup each and every search key in the Query result first column and where ever the match found, it may return the second column value from the Query result. The Vlookup behaves like an array with the help of ArrayFormula. That’s all.
If you find it difficult to understand, apply all the above steps in your own Google Sheet. If any part of this tutorials seems tough for you, please don’t hesitate to contact me via comment form below. Enjoy!