formula to tell me which word in a column is the most popular

[tonywatsonhelp]

Hi Everyone,

i have a shhet and down column f we choose between 3 words "Sold", "lost" and "free"

All i want is a formula to tell me which of those three words has been used the most so count number of

"Sold", "lost" and "free"​

and if "free" is the highest give me the word "Free" etc.

please help if you can

Tony

 

[knacksc2]

=IF(MAX(COUNTIF(C4:C1048576,"sold"),COUNTIF(C4:C1048576,"free"),COUNTIF(C4:C1048576,"lost"))=COUNTIF(C4:C1048576,"sold"),"sold",IF(MAX(COUNTIF(C4:C1048576,"sold"),COUNTIF(C4:C1048576,"free"),COUNTIF(C4:C1048576,"lost"))=COUNTIF(C4:C1048576,"free"),"free",IF(MAX(COUNTIF(C4:C1048576,"sold"),COUNTIF(C4:C1048576,"free"),COUNTIF(C4:C1048576,"lost"))=COUNTIF(C4:C1048576,"lost"),"lost")))

check the case of Sold, free, lost etc.

 

[Eric W]

Try:

Sheet2​

	F	G	H
Most common
Sold	Free
Free
Sold
Lost
Lost
Lost
Lost
Free
Sold
Free
Sold
Lost
Free
Sold
Sold
Sold
Lost
Free
Free
Free
Free

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=INDEX(F2:F25,MODE(IF(F2:F25<>"",MATCH(F2:F25,F2:F25,0))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

This will do what you asked for but note that if there are ties, it will only report one of them...

=CHOOSE(MODE(LOOKUP(F1:F26,{"Free","Lost","Sold"},{1,2,3})),"Free","Lost","Sold")