count a pair of numbers in row in a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nik Anto - 13 Oct 2006 18:38 GMT

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

Reply to this Message

Bernie Deitrick - 13 Oct 2006 20:56 GMT

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

> Hello,
> my question is:
[quoted text clipped - 21 lines]
> 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

Reply to this Message

Rate this thread: