Count rows that contain multiple values in multiple columns

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
30
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>
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Place in G2 and copy down: =IF(COUNTIF(B2:G2,"="&1)+COUNTIF(B2:G2,"="&2)+COUNTIF(B2:G2,"="&7)=3,TRUE,FALSE)
 

sooshil

Board Regular
Joined
Feb 21, 2013
Messages
104
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
It would be easier to help if you could post data that is exactly representative of your actual data.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">David</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Andrew</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Steve</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">6</td><td style="text-align: right;;">8</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Bob</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Harry</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">John</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Luke</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Merged</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H1</th><td style="text-align:left">=--(<font color="Blue">AND(<font color="Red">COUNTIF(<font color="Green">B1:G1,1</font>)>0,COUNTIF(<font color="Green">B1:G1,2</font>)>0,COUNTIF(<font color="Green">B1:G1,7</font>)>0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H8</th><td style="text-align:left">=SUM(<font color="Blue">H1:H7</font>)</td></tr></tbody></table></td></tr></table><br />
 

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
30
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?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,754
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
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
 

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
30
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,627
Messages
5,625,954
Members
416,145
Latest member
Tobi

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
Top