I have the results of a Google form in a Google sheet, and the form has participants vote on a variety of options. Results for one question would look like the following:
Activity
-------------
|Bowling |
|Karaoke |
|Karaoke |
|Games Night|
|Bowling |
|Karaoke |
What I want to do is have a query which will count the votes for each option in the column, then display the top result. So, for the information above, it would display "Karaoke". I have a query which works for this (below). I adapted this from a forum response, so I'm not fixed to this if there's a better way to do it.
=query(index(if({1,0},unique(A2:A12),countif(A2:A12,unique(A2:GX12)))),"select Col1, Col2 where Col1<>'' limit 1",0)
My question is, I can use "limit 1" in the query to only show one item, but what if there is a 2- or 3-way tie for the top choice? Is there a way that I can show only one option if it's the only max, but show 2 if there's a 2-way tie for max, or 3 if there is a 3-way tie, etc.?
For example, in the data above, it will show just
|Karaoke |
But if the next person votes for Bowling, there will be a tie, so then I want to show
|Karaoke |
|Bowling |
Does that make sense? The solutions I've found so far are in SQL or postgreSQL, and don't work in Google Sheets.
Thanks in advance. --Eric
Try this formula:
=QUERY(ArrayFormula(QUERY(A:A&{"",""},"select Col1, count(Col2) group by Col1")),"select Col1 where Col2="&MAX(COUNTIF(A:A,A:A)),0)