worksheet function - Excel search for string in multiple cells, compare and display string with most counts

Excel 2013

I have a table as follow:

    A
1   CHI
2   SEA
3   SEA
4   LA
...

How do I write a formula that can check cells A1 ~ A4 and display on another cell the string with most count? In this example, B1 displays SEA has the most count.

    B
1   SEA

To simplify this task, let's say CHI, SEA, LA are pre-determined entries. I could use a table to keep the counts:

    M    N
1   CHI  1
2   SEA  2
3   LA   1

But I would need help on writing formula for N1 ~ N3 to populate the count.

A variant of this question can be found here: Excel search for substring and display count

Answer

To get the count in N1 put:

=COUNTIF($A:$A,M1)

then you can use this formula to find the value with the most:

=INDEX(M:M,MATCH(MAX(N:N),N:N,0))

---

To return the value that is repeated the most with one formula, use this array formula:

=INDEX($A$1:$A$4,MATCH(MAX(COUNTIF($A$1:$A$4,$A$1:$A$4)),COUNTIF($A$1:$A$4,$A$1:$A$4),0))

Being an array it needs to be confirmed with Ctrl-Shift-Enter. If done properly then Excel will put {} around the formula.

With array formulas we want to reference only the ranges with data, and not use full column references.