Counting rows based on a condition

[azizrasul]

I have a table called 'tblSoftwareProducts' which contains 2 columns i.e. softwareproductname and chosenproduct.
Typical generic data could be something like the following

softwareproductname chosenproduct
Product1 Yes
Product2 No
Product3 Yes

I am looking for some VBA code that counts the number of rows, less the header, where the chosenproduct is equal to Yes.

 

[GWteB]

Perhaps you're after something like this ...

Public Sub azizrasul()
    Dim i As Long
    Application.ScreenUpdating = False
    With ActiveSheet.ListObjects("tblSoftwareProducts").Range
        .AutoFilter Field:=2, Criteria1:="yes"
        i = .SpecialCells(xlCellTypeVisible).Rows.Count
        .AutoFilter
    End With
    Application.ScreenUpdating = True
    MsgBox i
End Sub

 

[Fluff]

Another option

Sub azizrasul()
   MsgBox Evaluate("COUNTIF(tblSoftwareProducts[chosenproduct],""yes"")")
End Sub

 

[azizrasul]

Another option

Sub azizrasul()
   MsgBox Evaluate("COUNTIF(tblSoftwareProducts[chosenproduct],""yes"")")
End Sub

Thanks for that.

GWteB, your solution gave me an extra count by 1. I had a header plus two records with YES and I got a count of 3 instead of 2.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[GWteB]

GWteB, your solution gave me an extra count by 1. I had a header plus two records with YES and I got a count of 3 instead of 2.

Not sure what went wrong since it gave me under different conditions always the correct count.
Glad Fluff's code works for you.

 

[Fluff]

@GWteB
You code will include the header row and will only count the number of rows until the 1st No. So if the first data row has a No, your code will return 1, regardless of the number of visible rows.

 

[GWteB]

@Fluff
I notice that now too, thanks for your explanation

 

[Fluff]

My pleasure.
FYI your approach will work if you do it like

        i = .Columns(1).SpecialCells(xlCellTypeVisible).Count - 1