Count Rows

[admiral100]

Hi,

I am looking for a formula or VBA that count only the rows containing the word Yes.
In this example the result should be 3 (row 1,3,4)

A B C
[TABLE="width: 210"]
<colgroup><col width="70" span="3" style="width:52pt"> </colgroup><tbody>[TR]
[TD="width: 70, align: left"] Yes[/TD]
[TD="width: 70, align: left"] No[/TD]
[TD="width: 70, align: left"]1 No[/TD]
[/TR]
[TR]
[TD="align: left"] No[/TD]
[TD="align: left"] No [/TD]
[TD="align: left"]2 No[/TD]
[/TR]
[TR]
[TD="align: left"] Yes[/TD]
[TD="align: left"] Yes[/TD]
[TD="align: left"]3 Yes[/TD]
[/TR]
[TR]
[TD="align: left"] No[/TD]
[TD="align: left"] No[/TD]
[TD="align: left"]4 Yes [/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[Fishboy]

Hi,

I am looking for a formula or VBA that count only the rows containing the word Yes.
In this example the result should be 3 (row 1,3,4)

A B C
[TABLE="width: 210"]
<tbody>[TR]
[TD="width: 70, align: left"] Yes[/TD]
[TD="width: 70, align: left"] No[/TD]
[TD="width: 70, align: left"]1 No[/TD]
[/TR]
[TR]
[TD="align: left"] No[/TD]
[TD="align: left"] No[/TD]
[TD="align: left"]2 No[/TD]
[/TR]
[TR]
[TD="align: left"] Yes[/TD]
[TD="align: left"] Yes[/TD]
[TD="align: left"]3 Yes[/TD]
[/TR]
[TR]
[TD="align: left"] No[/TD]
[TD="align: left"] No[/TD]
[TD="align: left"]4 Yes[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

Hi admiral100, are you opposed to using a helper column? That will be the easiest way of doing it.

In column D (or a hidden helper column): =IF(COUNTIF(A1:C1,"Yes")>0,1,0)
At the bottom of that helper column you can have =COUNTIF(D1:D4,1)

You can update the bold red ranges to suit your data

Excel 2010

	A	B	C	D	E
Yes	No	No	<--- =IF(COUNTIF(A1:C1,"Yes")>0,1,0)
No	No	No
Yes	Yes	Yes
No	No	Yes
<--- =COUNTIF(D1:D4,1)

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

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

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

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

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

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

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

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

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

</tbody>

Sheet1​

 

[admiral100]

Hi Fishboy ,

In this case I prefer only one formula or code....
thanks anyway

 

[AkaTrouble]

this appears to work for me on your example data

=COUNTIFS(A1:C4,"yes")

EDIT on test of larger data it collapses SORRY

 

[admiral100]

Hi AkaTrouble ,
your formula returns the value 5

5 cell in the range that contain the word Yes...

 

[Weazel]

maybe something like...

=SUM(IF(FREQUENCY(IF(A1:C4="yes",ROW(A1:A4)-ROW(A1)+1),ROW(A1:A4)-ROW(A1)+1),1)) control shift enter

 

[JackDanIce]

If you want a code based suggestion, try:

Sub YesCount()
    
    Dim x       As Long
    Dim y       As Long
    Dim a       As Long
    Dim arr()   As Variant

    Const YES   As String = "Yes"
    
    arr = Cells(1, 1).CurrentRegion.value
    
    For y = LBound(arr, 2) To UBound(arr, 2)
        For x = LBound(arr, 1) To UBound(arr, 1)
            If arr(x, y) = YES Then
                a = a + 1
                Exit For
            End If
        Next x
    Next y
    
    Erase arr
    
    If a > 0 Then MsgBox "Number of rows with ""Yes"" " & vbCrLf & vbCrLf & a, vbOKOnly
                         
End Sub

 

[Fishboy]

Hi Fishboy ,

In this case I prefer only one formula or code....
thanks anyway

Try:

Sub CountYesRows()
Dim Cell As Range, cRange As Range, TotalRows As Integer
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set cRange = Range("A1:A" & LastRow)
For Each Cell In cRange
    If Application.WorksheetFunction.CountIf(Range("A" & Cell.Row, "C" & Cell.Row), "Yes") > 0 Then
        TotalRows = TotalRows + 1
    End If
Next Cell
Range("A" & LastRow + 1).Value = TotalRows
End Sub

This will allow your list to get as long as required. If the columns go further across than C, just change the "C" & Cell.Row to the correct letter.

 

[admiral100]

Bingo !

Thanks a lot Fishboy, your second code did the trick

 

[Fishboy]

Bingo !

Thanks a lot Fishboy, your second code did the trick

Yay! Happy to help