1

I'm trying to see how many times a person inputs the same answer into a form, and then take the top three people to use that certain answer. For example, people enter the "B Option", and I want to see the top three people to select "B Option" and how many times they did so.

This means I need to see A: all the instances of "B Option" getting picked (which is stored in column D), B: figure out which people did it the most (names stored in column A), and C: display both the names of the top users and the number of times they picked "B Option" somewhere.

I'll be honest, I have no idea how to even approach this issue.

Improve this question
1

1 Answer 1

Reset to default
0

This answer creates a single formula to output the analysis that you want.

=query(A1:D18,"select A, COUNT(D) where D='B' group by A order by Count(D) desc limit 3 label A 'Name' ")

  • The data is analysed using a query
  • where D='B'selects only the "B" answers
  • select A, COUNT(D) - returns the name and the frequency of "B" answers
  • order by Count(D) desc - returns the data sorted by the frequency column, descending (highest values first)

Sample

Sample

Improve this answer
2
  • This is amazingly helpful, thank you! Just trying to get it to fit my situation a little better. So far I have the following; let me know where I'm going wrong =query(C2:G656,"select C, COUNT(G) where G=$L2 group by C order by Count(G) desc limit 3")
    – CS-PP
    Oct 6, 2021 at 14:29
  • “where I'm going wrong” Sorry for the rudeness of my previous reply. Of course, I can help. There could be several reasons that your formula won't work but I do need to see your data, etc. Would you please share a spreadsheet containing some data? Thanks.
    – Tedinoz
    Oct 8, 2021 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.