Counting rows with specific number of populated cells

[gowildcats]

I want to count the number of rows that have a specific number of populated cells. For example looking at the table below, I want to know the number of rows that have "Yes" exactly 2 times. I only care about text though that's in columns C, D, E, and F (A, B, G, and H should not be included in my count). Eventually I want to replicate this same formula to count the number of cells in C through F that have 3 cells populated as well. My actual data has over 500 total rows and is formatted in a table. I've tried using different combos of CountIF and CountA but can't figure out the right equation combo.

[TABLE="width: 634"]
<colgroup><col><col span="8"></colgroup><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]
[TD]H[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[TD] [/TD]
[TD]Yes[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Try:

Sub countYes()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    Dim y As Long
    y = 0
    For x = 1 To LastRow
        If WorksheetFunction.CountIf(Range("C" & x & ":F" & x), "Yes") = 2 Then
            y = y + 1
        End If
    Next x
    MsgBox (y & " rows contain 2 occurrences of Yes.")
    Application.ScreenUpdating = True
End Sub

 

[DRSteele]

if you don't want VBA and can tolerate a helper column, here is one way to go:

Excel 2012
	A	B	C	D	E	F	G	H	I	J
1							rows with No of "Yes"
2							1	2	3	4
3							2	3	1	1
4						# of "Yes"
5	Yes	Yes	Yes			3
6			Yes			1
7	Yes			Yes		2
8						0
9		Yes		Yes		2
10	Yes	Yes				2
11				Yes		1
12	Yes	Yes	Yes	Yes		4
Sheet63

Cell Formulas
Range	Formula
G3	=COUNTIF($F:$F,G2)
H3	=COUNTIF($F:$F,H2)
I3	=COUNTIF($F:$F,I2)
J3	=COUNTIF($F:$F,J2)
F5	=COUNTIF(A5:D5,"=yes")
F6	=COUNTIF(A6:D6,"=yes")
F7	=COUNTIF(A7:D7,"=yes")
F8	=COUNTIF(A8:D8,"=yes")
F9	=COUNTIF(A9:D9,"=yes")
F10	=COUNTIF(A10:D10,"=yes")
F11	=COUNTIF(A11:D11,"=yes")
F12	=COUNTIF(A12:D12,"=yes")

 

[gowildcats]

Thanks! Anyway to do it in excel (not VBA) without a helper column? Will settle for the extra column if I have to, but anyway to code it without?

 

[ndsutherland]

I believe this will do it for you

=SUMPRODUCT((((C2:C9="Yes")+(D2:D9="Yes")+(E2:E9="Yes")+(F2:F9="Yes"))=2)*1)

 

[gowildcats]

Yes!!! Thanks so much - never would've gotten that!

i believe this will do it for you

=sumproduct((((c2:c9="yes")+(d2:d9="yes")+(e2:e9="yes")+(f2:f9="yes"))=2)*1)