count a pair of numbers in row in a table

[Nik Anto]

Hello,
my question is:
we have the following table:

34 29 13 15 7
15 8 40 11 24
13 6 8 21 38
9 17 23 1 4
22 38 42 37 16
1 18 11 37 41
5 42 18 33 45
9 1 21 41 15
41 1 27 23 42
23 29 7 38 18
42 12 26 34 36


and this one in another sheet
1 2 3
1
2
3


I want to fill the second table with the sum of how many times the numbers
if each row and column appear in the same row in the first table. for
example: how many time the numbers 2 and 3 appear together in the same row
on the first table

 

[Bernie Deitrick]

Nik,

Assume that you table starts in column A, row 2, and is 5 columns wide. In cell F2, use the formula

=" " & A2&" " &B2&" " &C2&" " &D2&" " &E2 & " "

and copy down to match your table.

Then, on the other sheet, with your numbers in row 1 and in column A, in cell B2, use this formula

=COUNTIF('Sheet with table'!$F$2:$F$19,"* " & B$1 & " * " & $A2 & " *") + COUNTIF('Sheet with
table'!$F$2:$F$19,"* " & B$1 & " " & $A2 & " *")

Change the 19 to match the row of the last formula from above.

Then copy this formula to match your table.

You may want to use

=COUNTIF('Sheet with table'!$F$2:$F$19,"* " & B$1 & " * " & $A2 & " *") + COUNTIF('Sheet with
table'!$F$2:$F$19,"* " & B$1 & " " & $A2 & " *") + COUNTIF('Sheet with table'!$F$2:$F$19,"* " & $A2
& " * " & B$1 & " *") + COUNTIF('Sheet with table'!$F$2:$F$19,"* " & $A2 & " " & B$1 & " *")

which will count without regard to order.

HTH,
Bernie
MS Excel MVP