I have a table in Google Sheets with a structure similar to this:
| Player | Hometown | Points |
|---|---|---|
| Amy | Mapleton | 25 |
| Amy | Mapleton | 50 |
| Amy | Mapleton | 150 |
| Bret | Jersey | 10 |
| Colin | Hocksfield | 5 |
| Colin | Hocksfield | 15 |
| David | Mapleton | 200 |
| Ella | Benning | 70 |
| Fred | Hocksfield | 20 |
| George | Jersey | 50 |
| Harold | Dover | 30 |
Except there's about 330 unique hometown values.
I'm trying to get a count of the hometowns that have the most number of players, which would look something like this based on the data above:
| Hometown | Players |
|---|---|
| Mapleton | 2 |
| Hocksfield | 2 |
| Jersey | 2 |
| Benning | 1 |
| Dover | 1 |
(There are only 2 players from Mapleton, even though there are 4 "point records" for Mapleton.)
Can somebody help me with a query that would return how many players there are in each hometown, that I can use in a Google Sheet?
CodePudding user response：
You may use QUERY() function like-
=QUERY(UNIQUE(A2:B),
"select Col2, count(Col1)
where Col2 is not null
group by Col2
order by count(Col1) DESC
label Col2 'Hometown', count(Col1) 'Playes'")
CodePudding user response：
Use this
=ArrayFormula({
$B$1,$A$1; UNIQUE(B2:B), IF(UNIQUE(B2:B)="",,
COUNTIF(QUERY(UNIQUE($A$2:$B)," Select Col2"),"="&UNIQUE(B2:B)))})
