Sum up rows with text

[adamsm]

Hi anyone,

How could I figure out a code or formula that would sum up all the rows containing the text "My" in column "K" of the active sheet to cell "A1".

My data starts from row 17; meaning the column headers are in row 16.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[adamsm]

One last help If I may ask. The code sum ups all the rows where hidden or visible. How could it be changed in a manner so that it would sum up only the visible rows?

 

[JackDanIce]

Try:

Sub CountMy ()
Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long
Range("A1:B1").ClearContents
For i = 6 To Range("K" & Rows.Count).End(xlUp).Row
  If Rows(i).Hidden = False Then
    If InStr(Range("K" & i), "My") > 0 Then Range("A1") = Range("A1").Value + 1
    If InStr(Range("K" & i), "Cat") > 0 Then Range("B1") = Range("B1").Value + 1
  End If
Next i
Application.ScreenUpdating = True
End Sub

 

[adamsm]

One more help if I may ask...I hope I'm not too late for this.

I'm having text in the format My/Family and My.

But the code counts My/family as 2 and My as 1 even though there is only one row of text containing My and My/Family?

How could I overcome this?

Any help on this would be kindly appreciated.

Thanks in advance.