How to calculate the number of pairs in an Excel spreadsheet?

Question

I have two columns of integers between 1 and 16 in an excel file. I'd like to count the number of pairs of integers in these columns. There are 256 cases and I'd like to have a column which tells me how many pairs exist for each case. For instance, I have a table like below:

1 2

1 1

1 3

1 4

1 1

1 8

1 1

16 16

1 2

...

And I'd like to calculate a column like this:

3 (number of 1 1s)

2 (number of 1 2s)

1 (number of 1 3s)

1 (number of 1 4s)

0 (number of 1 5s)

0 (number of 1 6s)

0 (number of 1 7s)

1 (number of 1 8s)

...

1 (number of 16 16s)

I'd appreciate if someone can help me with the calculation.

Answer 1

First you need to create two columns with all possible combinations:

1 1
1 2
1 3
...
2 1
2 2
...
16 16

Let's assume these are in columns C,D and your data are in columns A, B, in rows 1 to 1000. Then you can use an array formula:

=SUM(IF(($A$1:$A$1000=C1)*($B$1:$B$1000=D1);1;0))

You must press Shift+Ctrl+Enter when entering array formula.