Most and least common

  • Thread starter Thread starter jmj713
  • Start date Start date
J

jmj713

I need to find both the most and the least common text values in a column.
For instance, if I have ten entries reading "Tuesday" and three "Monday", I'd
like to be able to return "Tuesday" as the most common and "Monday" as the
least common value.
 
Assuming you have your values in Col A
then type this in B1
=INDIRECT("A"&MATCH(MAX(COUNTIF(A1:A30,A1:A30)),COUNTIF(A1:A30,A1:A30),0))
and
IMP:- press CTRL-SHIFT-ENTER as this is an ARRAY formula, to get the Max
entry..

and this for MIN in B2
=INDIRECT("A"&MATCH(MIN(COUNTIF(A1:A30,A1:A30)),COUNTIF(A1:A30,A1:A30),0))
 
Back
Top