Count rows that contain multiple values in multiple columns

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
44
Office Version
  1. 365
Platform
  1. Windows
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

ABCDEFG
1David1478910
2Andrew124579
3Steve2346810
4Bob3567910
5Harry146789
6John245678
7Luke346789

<tbody>
</tbody>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Place in G2 and copy down: =IF(COUNTIF(B2:G2,"="&1)+COUNTIF(B2:G2,"="&2)+COUNTIF(B2:G2,"="&7)=3,TRUE,FALSE)
 
Upvote 0
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.
 
Last edited:
Upvote 0
It would be easier to help if you could post data that is exactly representative of your actual data.
 
Upvote 0
How about


Book1
ABCDEFGH
1David14779100
2Andrew1245791
3Steve23468100
4Bob35679100
5Harry1477890
6John2456780
7Luke3467890
81
Merged
Cell Formulas
RangeFormula
H1=--(AND(COUNTIF(B1:G1,1)>0,COUNTIF(B1:G1,2)>0,COUNTIF(B1:G1,7)>0))
H8=SUM(H1:H7)
 
Upvote 0
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?
 
Upvote 0
Using the layout of the latest file, here's a single-cell solution:

Excel 2012
ABCDEFGHIJKLMNOP
136102
2116thApril1988368101326TRUE
3223rdApril198872127303536FALSE
4330thApril198811019202229FALSE
547thMay198851012252731FALSE
6514thMay198821013283236FALSE
7621stMay19886712202830FALSE
8728thMay19884918333536FALSE
984thJune1988167112236FALSE
10911thJune19882810142124FALSE
111018thJune1988131820222328FALSE

<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>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
P1=SUMPRODUCT(--(MMULT(SIGN(COUNTIF(OFFSET(E2:J2,ROW(E2:E51)-ROW(E2),0),L1:N1)),{1;1;1})=3))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



If you want to look for more or less than 3 values, we'd have to change the {1;1;1} array value.
 
Upvote 0
Or with a macro
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top