In Google Sheets, how can you find the mode of non-numerical data?

Question

If I want to find the mode of A2:A10, but my values are something like:

Foo
Foo
Bar
Foo
Baz
Bar
Foo
Bar
Foo

, what can I do?

The =MODE() function only takes numerical data, so is there a way to find the most frequent of these values? Thank you!

score 12 · Accepted Answer · answered Feb 27 '16 at 14:37

12

Use a MATCH on a MAX(COUNTIF(...)) to a COUNTIF and pass the row position back to an INDEX.

=index(A2:A10, match(max(countif(A2:A10, A2:A10)), countif(A2:A10, A2:A10), 0))

In the case of a tie in frequency, the first in the list that matches the maximum frequency rate will be returned.

answered Feb 27 '16 at 14:37

1

Just wondering, why does this work? (BTW, it worked like a charm) – AAM111 Feb 27 '16 at 14:40
The [google-sheets] formula is processed like an Excel array formula. If you put it into an Excel worksheet with CSE you canSelect B2 and use the [Evaluate Formula](https://goo.gl/Vl25be) command. Repeat if necessary until you understand it. Keep the ranges small or the results from the cyclic processing can get confusing. – Feb 27 '16 at 14:45
1

I'm sorry, but I don't have Microsoft Excel. (I use OS X) – AAM111 Feb 27 '16 at 22:26
1

This doesn't work when you have more than one modes. – Irfandy Jip Jul 14 '19 at 15:07
what happen when there is multiple text value with same count..multiple modes? – charitha amarasinghe Jul 29 '19 at 13:20

score 0 · Answer 2 · answered Dec 15 '21 at 14:33

Try the following formula:

=ArrayFormula(index($A$1:$A$10, mode(match($A$1:$A$10,$A$1:$A$10,0))))

MATCH function will give you the position of each first entry, which is a numerical value and can be used inside MODE function, then we can use INDEX function to extract the value from this list.

In Google Sheets, how can you find the mode of non-numerical data?

2 Answers2