Forum Discussion
Excel Formula
Is there a Formula that can return the most used word in a Column?
Ex. Column E is preferred language.
How can I get excel to return the most used language in Column E
The formula doesn't work if the range contains blanks.
Here is an alternative that works even with blanks:
In F2:
=COUNTIF($E$2:$E$170,$E2)
Adjust the range if necessary, then fill down.
In G2:
=MAX(F2:F170)
In H2:
=INDEX(E2:E170,MATCH(G2,F2:F170,0))
10 Replies
Let's say the list of preferred languages is in E2:E170
=INDEX(E2:E170,MODE(MATCH(E2:E170,E2:E170,0)))
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
- I tried that earlier, I get #N/A error
The formula doesn't work if the range contains blanks.
Here is an alternative that works even with blanks:
In F2:
=COUNTIF($E$2:$E$170,$E2)
Adjust the range if necessary, then fill down.
In G2:
=MAX(F2:F170)
In H2:
=INDEX(E2:E170,MATCH(G2,F2:F170,0))
What exactly means "Column E is preferred language", what will be in E1, E2, etc. ?
- All of Column E will be various languages. The question being Preferred Language and the language spoken will be entered into Column E.
Ex. Column E2 = Portuguese Column E3= Spanish and so on, I am looking for a formula that can taken the different languages and either calculate which language was entered most or a percentage for each language in all of Column E
