Frequency distribution

I have a worksheet with two columns of data

Column A has contains a reference code, d1, d2, d3....and so on to d6
however, this code can occur more than once.
Column B has a random number against the reference codes (could be anything
between 1 -100) e.g.

d1 5
d2 7
d1 10
d3 1
d1 5
d5 6
d4 20
d4 5
d6 15
d5 6
d1 10

I would like to calculate the number of occurances of each reference code
with each number, i.e. the number of d1's with number 5 = 2, the number of
d1's with 10 = 1.

Can anyone help?

Thanks

Place the following formula in an adjacent cell, substituting the ranges to
match yours

=COUNT(IF(A18&B18=A18:A23&B18:B23,B18:B23,""))

A18 represents the adjacent reference code, b18 represents the adjacent
number. a18:a23 is the full range of reference codes, which you should enter
in absolute format, whilst b18:b23 is the range of numbers which also should
be entered in absolute format. Finally, this formula needs to be entered as
an array, meaning, press cntrl-shift-enter after typing it in.

http://HelpExcel.com

"Ms MIS" wrote:

> I have a worksheet with two columns of data
>
> Column A has contains a reference code, d1, d2, d3....and so on to d6
> however, this code can occur more than once.
> Column B has a random number against the reference codes (could be anything
> between 1 -100) e.g.
>
> d1 5
> d2 7
> d1 10
> d3 1
> d1 5
> d5 6
> d4 20
> d4 5
> d6 15
> d5 6
> d1 10
>
> I would like to calculate the number of occurances of each reference code
> with each number, i.e. the number of d1's with number 5 = 2, the number of
> d1's with 10 = 1.
>
> Can anyone help?
>
> Thanks