List the most common value in a range

PatPat

Board Regular
Hello

Can someone please guide me as to what function to use so that I could extract the most common value in a range?

For example if the range I have contains the following

a
s
a
a
h
t
y
u
a
a

I would want the value a to be displayed.

Thanks in anticipation

p

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

NBVC

Well-known Member
If the list consists of one-letter strings as per you sample then possibly this will work:

=CHAR(MODE(CODE(B1:B10)))

else if the strings are multi-letter (e.g names), then try

=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0))

Where B1:B10 contains the range to look in.

The latter formula must be confirmed with Ctrl+Shift+Enter not just Enter. You should see {} brackets around the formula if entered correctly.

MrExcel MVP
Hello

Can someone please guide me as to what function to use so that I could extract the most common value in a range?

For example if the range I have contains the following

a
s
a
a
h
t
y
u
a
a

I would want the value a to be displayed.

Thanks in anticipation

p
MostCommonValue.xls
ABCDEFGH
125
2ValueFreqMost Common Value(s)Count of Value
3a5aValueTotal
4s1ua5
5a  u5
6a
7h2
8t2
9y2
10u5
11a
12a
13u
14t
15u
16u
17y
18u
19h
Sheet1

B1:

=COUNTIF(B3:B19,">="&MAX(B3:B19))

B3, copied down:

=IF(A3<>"",IF(ISNUMBER(MATCH(A3,$A$2:A2,0)),"",COUNTIF($A$3:$A$19,A3)),"")

E1:

=MAX(B3:B19)

E3:

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

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

The same output can also be obtained by running Data|PivotTable... on A2:A9 as can be seen in G:H on the exhibit.

Replies
0
Views
188
Replies
8
Views
706
Replies
12
Views
648
Replies
4
Views
342
Replies
7
Views
903

1,171,968
Messages
5,878,537
Members
433,352
Latest member
horna

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.

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