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]

 

[mumps]

Place in G2 and copy down: =IF(COUNTIF(B2:G2,"="&1)+COUNTIF(B2:G2,"="&2)+COUNTIF(B2:G2,"="&7)=3,TRUE,FALSE)

 

[sooshil]

Place in G2 and copy down: =IF(COUNTIF(B2:G2,"="&1)+COUNTIF(B2:G2,"="&2)+COUNTIF(B2:G2,"="&7)=3,TRUE,FALSE)

This formula fails when a number repeats and another number is absent. How can this problem be addressed?
I am here just to learn something. This may not be the requirement of the original question.
And, again the original question was to count the rows. This formula just delivers TRUE and FALSE for each row.

 

[mumps]

It would be easier to help if you could post data that is exactly representative of your actual data.

 

[Fluff]

How about

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

Cell Formulas
Range	Formula
H1	=--(AND(COUNTIF(B1:G1,1)>0,COUNTIF(B1:G1,2)>0,COUNTIF(B1:G1,7)>0))
H8	=SUM(H1:H7)

 

[Lewiskj]

Thanks for all your help.
I might have been a bit vague in my question I do apologise.
i have a sample file, how do I attach it to the post for you to download it?

 

[Lewiskj]

I realise thatt attachments are not allowed
here is a link to the file I would like help with.

https://1drv.ms/x/s!AnlQC4WN-LeXowQfAmequZdibFKs?e=2BELib

I hope its OK to link a file in this way within a post

 

[Eric W]

Using the layout of the latest file, here's a single-cell solution:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
16th	April
23rd	April
30th	April
7th	May
14th	May
21st	May
28th	May
4th	June
11th	June
18th	June

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P1[/TH]
[TD="align: left"]=SUMPRODUCT(--(MMULT(SIGN(COUNTIF(OFFSET(E2:J2,ROW(E2:E51)-ROW(E2),0),L1:N1)),{1;1;1})=3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If you want to look for more or less than 3 values, we'd have to change the {1;1;1} array value.

 

[Fluff]

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

 

[Lewiskj]

Thanks to all who helped, all solutions were great but I've gotta say...

Fluff... that's exactly what i was looking for and it runs perfectly.

I know its a little slow cos it's got loads of data to go through, but i thought it would be a lot slower.
...
Can't thank you enough for this you're a GENIUS