I need an excel function that will give me the "mode" of text val.

[Jared D.]

I have a list of text, in a column, and need to find out which text value
appears most often. Similar to the mode function for numerical values. Mode
only works for numbers.

 

[Harlan Grove]

I have a list of text, in a column, and need to find out which text value
appears most often. Similar to the mode function for numerical values.
Mode only works for numbers.

LEo Heuser came up with the array formula

=INDEX(ColRng,MODE(MATCH(ColRng,ColRng,0)))

 

[Biff]

Hi Jared!

Try this, entered as an array, CTRL,SHIFT,ENTER:

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

This will return the first instance of the text value that
appears most frequently. For example, if you have the
words Tom and John, and both appear 5 times but Tom
appears before John in the column, Tom will be the
returned value.

Biff

 

[Don]

Jared:

The most frequent value:

=INDEX(Myrange,MATCH(MAX(COUNTIF(Myrange,Myrange)),COUNTIF(Myrange,Myrange),0),1)


The count of the most frequent value:

=COUNTIF(Myrange,INDEX(Myrange,MATCH(MAX(COUNTIF(Myrange,Myrange)),COUNTIF(Myrange,Myrange),0)))

Both are array formulas

Don Pistulka