Summary of all values and occurrence of each value

[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 telling me how many times each number occours. There can be gaps between these numbers, lets say the number 90 occurs 12 times, number 95 occurs 2 times, number 98 occurs 5 times and so on and on.

Help would be very much appreciated, thanks

[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)


"martho" <[email protected]> wrote in
message news:[email protected]...
>
> 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 telling
> me how many times each number occours. There can be gaps between these
> numbers, lets say the number 90 occurs 12 times, number 95 occurs 2
> times, number 98 occurs 5 times and so on and on.
>
> Help would be very much appreciated, thanks
>
>
> --
> martho
> ------------------------------------------------------------------------
> martho's Profile:
http://www.excelforum.com/member.php...o&userid=29144
> View this thread: http://www.excelforum.com/showthread...hreadid=488627
>