Summary of all values and occurrence of each value

M

martho

I got a problem where I am looking for an elegant solution.

I got an table containing data, telling me a certain number.
What I want is a summary of all the numbers in that table and tellin
me how many times each number occours. There can be gaps between thes
numbers, lets say the number 90 occurs 12 times, number 95 occurs
times, number 98 occurs 5 times and so on and on.

Help would be very much appreciated, thank
 
B

Bob Phillips

Assuming the numbers are in A1:A100

B1: =A1
B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

which is an array formula, so commit with Ctrl-Shift-Enter, and copy doun

C1: + COUNTIF(A:A,B1)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top