# Most Common Occurance

#### Nick147

##### Board Regular
How do I create a formula that will return the value (i.e. list of names), that occurs the most in a given array? For example, a formula that would return "Nick" from the following list: Bob, Nick, Bill, Nick, Mike, Bob, Nick.

Thanks,
Nick147

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
=INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNTIF(A1:A7,A1:A7),0)), which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar)

If your list is in A1:A10 and you have no blanks in there then try

=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))

if you do have blanks use

=INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))))

confirmed with CTRL+SHIFT+ENTER

That worked great! Thanks.

I'm having trouble with barry houdini's formula:

=INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))))

It appears to only work if I have 2 or more cells in the array that have a value greater than "". So I"m having trouble when I only have one cell in the array that has a value greater than "". Can anyone help?

Hi Nick147

Oaktree's solution seems to work even in that case. Have you tried it?

HTH
PGC

How do I create a formula that will return the value (i.e. list of names), that occurs the most in a given array? For example, a formula that would return "Nick" from the following list: Bob, Nick, Bill, Nick, Mike, Bob, Nick.

Thanks,
Nick147
ModeOrMostCommonTextItem (Top 1).xls
ABCDEF
113
22Most Freq Name(s)
3NameFreqNick
4Bob2Benoit
5Nick3
6Bill1
7Nick
8Mike1
9Bob
10Nick
11Benoit3
12Benoit
13
14Benoit
15
Sheet1

B1: 1

B2:

=COUNTIF(B4:B15,">="&LARGE(B4:B15,B1))

B4, copied down:

=IF(A4<>"",IF(ISNUMBER(MATCH(A4,\$A\$3:A3,0)),"",COUNTIF(\$A\$4:\$A\$15,A4)),"")

E1:

=MAX(Sheet1!B4:B15)

E3:

=IF(ROWS(\$E\$3:E3)<=\$B\$2,INDEX(\$A\$4:\$A\$15,SMALL(IF(\$B\$4:B\$15=\$E\$1,ROW(\$B\$4:B\$15)-ROW(B\$4)+1),ROWS(\$E\$3:E3))),"")

which is confirmed with control+shift+enter (not with enter) then copied down.

Hi,

Try Pivot table.

HTH

Replies
0
Views
101
Replies
4
Views
320
Replies
9
Views
164
Replies
2
Views
421
Replies
14
Views
727

1,221,494
Messages
6,160,141
Members
451,624
Latest member
TheWes

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back