Most Frequent Text Value in Excel

[honorbum]

I am trying to figure out how to look at a column of City names and in a cell, display the City name which occurs most often. Only formula I can find is "Mode" which will only give the most frequent "number" that occurs and not text.

 

[SkipVought]

Hi,

Check out the pivot table wizard, Count of City

Skip,

_[red][/red]

 

[honorbum]

I know it can be done in a pivot table but I don't have room to display the table. I was hoping there was a single cell formula I could use.

 

[SkipVought]

so you hide what you don't want to see and pick the Top 1.

Skip,

_[red][/red]

 

[xlhelp]

use the COUNTIF function

Me transmitte sursum, Caledoni!

 

[honorbum]

I don't see how "COUNTIF" would work in this case? Also, I'm trying to avoid using a pivot in this case.

To expand: I have a list of trouble tickets for a specific regiona and I am simply trying to put one line in a report which reads: City with Most Tickets - (city name).

 

[SkipVought]

Use MS Query to get

Select MAX(City) From ThatSheet$

Skip,

_[red][/red]

 

[honorbum]

MAX gives the largest numeric value of the text string. Doesn't work.

 

[SkipVought]

I was not thinking, sorry.

Select Count(*) From ThatSheet$
Where City = (Select MAX(City) From ThatSheet$)

Skip,

_[red][/red]

 

[honorbum]

I gave in to the Pivot, created it on another sheet and then just cell referenced the Top city. I appreciate your help though Skip.

 

[dcompto]

The following is an array, so be sure to press Ctrl+Shift+Enter after entering/pasting it into the formula bar:

=INDEX(A2:A12,MODE(IF(A2:A12<>0,MATCH(A2:A12,A2:A12,0))))

P.S.
I can't take credit for it. I had it in my personal knowledge base and it originally came from

http://www.eggheadcafe.com/software/aspnet/28203947/another-match-and-index-p.aspx

 

[dcompto]

Of course, the MAX count could pertain to more than one city. The array I previously posted will return the firstcity that equals the MAX count. Not sure how you would want to handle that.

 

[honorbum]

Thanks dcompto