How do I return the most frequently occurring text value in range of text values in a Google Docs spreadsheet?

Question

I have a range B2:B7 which contains text values that stand for something, but what I want is to have a cell display the text value that comes up the most in that range.

So for example if my range contained the text values {"red", "green", "green", "blue", "green", "blue"}, the average value displayed in the cell would be "green".

How do I do this?

Answer 1

=SORTN(A1:A7,1,2,COUNTIF(A1:A7,A1:A7),0)

Answer 2

I found a solution that worked for my actual problem that was simplified in my question. (It's a record of every hour of my year so far.)

The first row is the average day.

I used chiliNUT's method in this answer, in which he provided this nice and simple line that solved my specific case (assuming the values are in the range A1:A7):

=INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNTIF(A1:A7,A1:A7),0))

Yes I had to copy and edit it 24 times but it's worth it. Thanks to those who answered too.

Answer 3

Alternate solution which I found here: https://productforums.google.com/forum/#!topic/docs/4cpTjqVH0bs

Assuming your values are in column A you can get a result showing the unique values and their corresponding counts using the following:

=QUERY({A:A,A:A},"Select Col1, Count(Col2) Where Col1<>'' Group by Col1 Label Col1 'Name',Count(Col2) 'Count'")

More information on the QUERY function here: https://support.google.com/docs/answer/3093343?hl=en

Answer 4

You can do this by adding a numeric column which will allow you to count each row easily.

Then check for unique values in column A

=UNIQUE(A2:A7)

The result in this example would be

Then count the frequency of each unique value using a sumif function =sumif(A:A,E1,B:B)

Last lookup the word that corresponds with the max value =vlookup(max(D:D),D:E,2,false) (cell G2 below)