Forum Discussion
Solved
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))
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))
- It worked.. Thank you so much. 🙂
Didn't test, but it returns #N/A if you have blank cells in the range.
- No with information entered in cells
