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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]David[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Andrew[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Steve[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bob[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Harry[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Luke[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.