Most Frequent Letter in a Column of Four Different Letters

[valmir]

Hello everyone:
I'm using this formula:
=IF(COUNTIF(G3:G14,"b")>MAX(COUNTIF(G3:G14,"c"),COUNTIF(G3:G14,"q")),"b",IF(COUNTIF(G3:G14,"c")>COUNTIF(G3:G14,"q"),"c","q"))
that I found on this post Here
However this formula works perfectly but only for three letters, not four as I need.
Can anyone help me?

 

[kanadaaa]

Of course it doesn't because the formula only involves "b", "c", and "q" and there's no fourth letter involved.
Say, the fourth letter is "a". Then this should work:

=IF(COUNTIF(G3:G14,"b")>MAX(COUNTIF(G3:G14,"c"),COUNTIF(G3:G14,"q"),COUNTIF(G3:G14,"a")),"b",IF(COUNTIF(G3:G14,"c")>MAX(COUNTIF(G3:G14,"q"),COUNTIF(G3:G14,"a")),"c",IF(COUNTIF(G3:G14,"q")>COUNTIF(G3:G14,"a"), "q", "a"))

 

[valmir]

Of course it doesn't because the formula only involves "b", "c", and "q" and there's no fourth letter involved.
Say, the fourth letter is "a". Then this should work:

=IF(COUNTIF(G3:G14,"b")>MAX(COUNTIF(G3:G14,"c"),COUNTIF(G3:G14,"q"),COUNTIF(G3:G14,"a")),"b",IF(COUNTIF(G3:G14,"c")>MAX(COUNTIF(G3:G14,"q"),COUNTIF(G3:G14,"a")),"c",IF(COUNTIF(G3:G14,"q")>COUNTIF(G3:G14,"a"), "q", "a"))

Thanks a lot. It works! Just one more thing. If there is no match it will return an "a" value, any way to prevent that, like returning an empty cell in case of no match?

 

[Fluff]

How about

=IFNA(INDEX(G3:G14,MODE(IF(G3:G14<>"",MATCH(G3:G14,G3:G14,0)))),"")

This needs to be confirmed with Ctrl Shift Enter, rather than just Enter.

 

[kanadaaa]

If there is no match it will return an "a" value, any way to prevent that, like returning an empty cell in case of no match?

Try:

=IF(COUNTIF(G3:G14,"b")>MAX(COUNTIF(G3:G14,"c"),COUNTIF(G3:G14,"q"),COUNTIF(G3:G14,"a")),"b",IF(COUNTIF(G3:G14,"c")>MAX(COUNTIF(G3:G14,"q"),COUNTIF(G3:G14,"a")),"c",IF(COUNTIF(G3:G14,"q")>COUNTIF(G3:G14,"a"), "q",IF(COUNTIF(G3:G14,"a")>0,"a",""))

 

[valmir]

Try:

=IF(COUNTIF(G3:G14,"b")>MAX(COUNTIF(G3:G14,"c"),COUNTIF(G3:G14,"q"),COUNTIF(G3:G14,"a")),"b",IF(COUNTIF(G3:G14,"c")>MAX(COUNTIF(G3:G14,"q"),COUNTIF(G3:G14,"a")),"c",IF(COUNTIF(G3:G14,"q")>COUNTIF(G3:G14,"a"), "q",IF(COUNTIF(G3:G14,"a")>0,"a",""))

Exactly! Works perfectly! Thank you so much for your help, sir!

 

[kanadaaa]

Exactly! Works perfectly! Thank you so much for your help, sir!

Glad to know it helped. But Fluff's approach in #4 is more elegant, if you choose not to stick to IF formulas.

 

[valmir]

Glad to know it helped. But Fluff's approach in #4 is more elegant, if you choose not to stick to IF formulas.

Didn't work for me. was returning a "#NAME?" error. Maybe wrong Office version? I don't know.

 

[Fluff]

Oops IFNA didn't appear until 2013.
If you change that to IFERROR it should work.

 

[kanadaaa]

Didn't work for me. was returning a "#NAME?" error. Maybe wrong Office version? I don't know.

IFNA works only for Excel 2013 or later. How about:

=IFERROR(INDEX(G3:G14,MODE(IF(G3:G14<>"",MATCH(G3:G14,G3:G14,0)))),"")