List the most common value in a range

PatPat

Board Regular
Joined
Jan 13, 2006
Messages
186
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,136,369
Messages
5,675,359
Members
419,565
Latest member
Phil57

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top