Most used word in certain cells.

[Dehaz]

Hi

I’m new to this forum and hope someone can help.

Basically I’m trying to work out a formula, preferably without using Macro or Java, in Excel to work out which football team is most used in certain cells.

For example if I wrote the following teams down in separate cells, being....

Inter Milan
Inter Milan
Real Madrid
Arsenal
Liverpool

I would like it to know that Inter Milan was the most used team with 2 hits. Although the only problem I have is I don't want the Formula to contain the teams in it. Because then I'm limited to the teams within that formula.

Hope that makes sense, and thanks for anyone who replies. Regards

[Cheeky Charlie]

How about:
=INDEX(B2:B38,MATCH(MAX(COUNTIF(B2:B38,B2:B38)),COUNTIF(B2:B38,B2:B38),0))

entered with Ctrl + shift + enter

replacing B2:B38 with your column of info

HTH

[Dehaz]

Hi

Thanks for your reply. I've tried changing the cells for the correct ones and it comes back with #N/A

How about:
=INDEX(B2:B38,MATCH(MAX(COUNTIF(B2:B38,B2:B38)),COUNTIF(B2:B38,B2:B38),0))

entered with Ctrl + shift + enter

replacing B2:B38 with your column of info

HTH

[martindwilson]

this also works and as a non array ,apparently should be preferred! but all cells in range must be used not blank ,normally entered
=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))

also array(ctr shift enter) in b2 dragged down will give them in order of most
=INDEX($A$1:$A$10,MODE(IF(COUNTIF(B$1:B1,$A$1:$A$10)=0,MATCH($A$1:$A$10,$A$1:$A$10,0))))
both these if there is a = distribution return the first found first

[Cheeky Charlie]

Are you using ctrl+shift+enter to enter the formula?

[martindwilson]

cheekcharlies works fine for me too

[Ron Coderre]

With
A1:A20 containing various text (some blanks allowed)

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER (instead of
just ENTER) returns the most occurring text:

Please Login or Register  to view this content.

Does that help?

[Dehaz]

Hi.... stupidly i didn't do the ctrl, alt enter as told to. So it was my fault. Thanks guys for all you're help!!!!!