# EXCEL- Counting pairs/quads of numbers in rows. How to count?

I have 300+ rows containing 40+ columns, each column has a number 1-40 at random.

I want to know how to search each row for pairs or numbers that occur together within the row.

Example.

``````5   10  12  14  16  17  19  30  31  32  33  40  51
4   7   11  16  17  23  24  26  32  37  39  46  47
1   2   4   7   11  15  18  31  34  35  37  38  43
3   6   14  16  19  21  24  27  33  34  38  42  47
5   7   8   15  17  22  23  24  26  27  30  37  40
``````

I want to know how many time 1 and 2 occur together or 23 and 24, or 5 and 30.

How would I do this? which formula would I use and how would I implement it.

I've looked at countifs, pivot tables, sumproducts, totals. And I am more confused than when I started.

• Are you trying to find every combination (1 and 2, 1 and 3, 1 and 4...), does order matter (is 1 2 the same as 2 1) do they actually need to be beside one another or just both occur in the row? – gtwebb May 9 '16 at 23:32

You need to combine both `IF`, `AND` and `COUNTIF`.

For example,

`=IF(AND(COUNTIF(A1:J1,23)>0,COUNTIF(A1:J1,24)>0),1,0)`

This solution yields 1, IF both 23 AND 24 occurred at least once from A1 to J1, and yields 0 if not both of them appear in the row. Because of the nesting involved, I would recommend a User Defined Function (UDF) to solve this.

This code ...

``````Function CountPairs(theRange As Range, P As Variant, Q As Variant) As Long
Dim PRow As Range
Dim PCell As Range, QCell As Range
Dim Result As Long
' initialize
Result = 0

For Each PRow In theRange.Rows      'search each row for P Value
For Each PCell In PRow.Cells
If PCell.Value = P Then
For Each QCell In PRow.Cells 'if P Value found, search for Q Value
If QCell.Value = Q Then
Result = Result + 1
Exit For
End If
Next QCell
Exit For
End If
Next PCell
Next PRow

' clean up
Set PRow = Nothing
Set PCell = Nothing
Set QCell = Nothing

CountPairs = Result

End Function
``````

Yields these results ...  ... where I used conditional formatting to highlight the "P" and "Q" values

However, with a "helper" column, it can be solved with built in formula ... Where, in this case the helper column contains this formula filled down ...

``````=IF(COUNTIF(\$B4:\$N4,"="&\$B\$2)>0,IF(COUNTIF(\$B4:\$N4,"="&\$C\$2)>0,1,0),0)
``````

and Cell A2 contains a simple sum ...

``````=SUM(A4:A8)
``````