Frequency distribution

[Guest]

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

 

[Guest]

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