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?

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

--
Gary''s Student - gsnu200725


"tht" wrote:

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?

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$2:D2,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

"tht" wrote in message
...
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?