How do I find the 6 most common

[Guest]

I have a lot of numbers in a data field. I cam find the most common. I know
how to make excel count each one. But can someont tell me how to make the
2nd and so on show up in the selected cell?

 

[Guest]

One way is it use a Pivot Table. Say we start with data like:

items
3
45
3
34
100
7
100
45
45
3
34
34
2
8
100
100
3
67
34
34
1
67
100
100

the pivot table would be:

Count of items
items Total
3 4
45 3
34 5
100 6
7 1
2 1
8 1
67 2
1 1
Grand Total 24


After sorting by Total we get:

Count of items
items Total
3 4
45 3
34 5
100 6
7 1
2 1
8 1
67 2
1 1
Grand Total 24

 

[T. Valko]

Try this:

Assumes there is at least 1 number that appears more than once.

Assume the numbers are in a named range called rng. rng = A2:A20

Enter this formula in D2:

=MODE(rng)

Enter this array formula** in D3 and copy down until you get blanks:

=IF(ROWS($1:2)<=COUNT(1/FREQUENCY(rng,rng)),MODE(IF(COUNTIF(D$22,rng)=0,rng+{0,0})),"")

Enter this formula in E2 and copy down as needed:

=IF(D2="","",COUNTIF(rng,D2))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff