Count rows that contain multiple values in multiple columns

[Lewiskj]

I would like to find some function or VBA code to count how many rows contain 3 specific numbers in columns B: to G:

i.e how many rows contain 1 & 2 & 7

	A	B	C	D	E	F	G
1	David	1	4	7	8	9	10
2	Andrew	1	2	4	5	7	9
3	Steve	2	3	4	6	8	10
4	Bob	3	5	6	7	9	10
5	Harry	1	4	6	7	8	9
6	John	2	4	5	6	7	8
7	Luke	3	4	6	7	8	9

<tbody>
</tbody>

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Lewiskj]

Or with a macro

Sub Lewiskj()
   Dim Ary1 As Variant, Ary2 As Variant
   Dim r As Long, rr As Long
 
   With Sheets("Data")
      Ary1 = .Range("E2", .Range("J" & Rows.Count).End(xlUp).Offset(, 1)).Value2
   End With
   With Sheets("Results")
      Ary2 = .Range("A2", .Range("C" & Rows.Count).End(xlUp)).Value2
   End With
   ReDim Preserve Ary2(1 To UBound(Ary2), 1 To UBound(Ary2, 2) + 2)
   For r = 1 To UBound(Ary1)
      Ary1(r, 7) = "|" & Join(Application.Index(Ary1, r, 0), "|") & "|"
   Next r
   For r = 1 To UBound(Ary2)
      For rr = 1 To UBound(Ary1)
         If InStr(1, Ary1(rr, 7), "|" & Ary2(r, 1) & "|") > 0 And InStr(1, Ary1(rr, 7), "|" & Ary2(r, 2) & "|") > 0 And InStr(1, Ary1(rr, 7), "|" & Ary2(r, 3) & "|") > 0 Then
            Ary2(r, 4) = Ary2(r, 4) + 1
            Ary2(r, 5) = Ary2(r, 5) & rr & ", "
         End If
      Next rr
   Next r
   Sheets("Results").Range("A2").Resize(UBound(Ary2), 5).Value = Ary2
End Sub

but it's a lot slower than Eric's formula

My data positions have altered so I have changed the above range from
Ary1 = .Range("E2", .Range("J" & Rows.Count).End(xlUp).Offset(, 1)).Value2
to
Ary1 = .Range("C2", .Range("H" & Rows.Count).End(xlUp).Offset(, 1)).Value2

But it still includes all numbers in column "I" so its using 7 columns instead of 6

I've tried a few alterations but can't seem to figure out why it's doint this

 

[Fluff]

Which columns should it be looking at?

 

[Lewiskj]

Which columns should it be looking at?

Columns C, D, E, F, G, H

 

[Fluff]

In that case it should be Ok as the last column in the array is being overwritten.