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.

 

[JackDanIce]

Try:

Sub CountMy ()
Application.ScreenUpdating = False
Dim i As Long, j As Long
j = 0
For i = 17 to Range("K" & Rows.Count).End(xlUp).Row
  If InStr(UCase(Range("K" & i)), "MY") > 0 Then j = j + 1
Next i
Range("A1") = j
Application.ScreenUpdating = True
End Sub

 

[adamsm]

Thanks for the help. In the same column I have the text "You".

With this situation how could I change the code so that it would sum up the text "My" in cell A1 and text "You" in cell B1.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[JackDanIce]

In that case try:

Sub CountMy ()
Application.ScreenUpdating = False
Dim i As Long, j As Long, k as Long
j = 0
k = 0
For i = 17 to Range("K" & Rows.Count).End(xlUp).Row
  If InStr(UCase(Range("K" & i)), "MY") > 0 Then j = j + 1
  If InStr(UCase(Range("K" & i)), "YOU") > 0 Then k = k + 1
Next i
Range("A1") = j
Range("B1") = k
Application.ScreenUpdating = True
End Sub

 

[Gerald Higgins]

Do you just want to count the cells that contain "MY" ?

If yes

=countif(K17:K100,"MY")

Put this in A1 if that's where you want to show it.

Change 100 to whatever is a suitable number for the length of your column.

 

[JackDanIce]

Alternatively, to make it a bit more generic, use:

Sub CountMy ()
Application.ScreenUpdating = False
Dim i As Long
Range("A1:B1").ClearContents
For i = 17 To Range("K" & Rows.Count).End(xlUp).Row
  If InStr(UCase(Range("K" & i)), "MY") > 0 Then Range("A1") = Range("A1").Value + 1
  If InStr(UCase(Range("K" & i)), "YOU") > 0 Then Range("B1") = Range("B1").Value + 1
Next i
Application.ScreenUpdating = True
End Sub

 

[adamsm]

Thanks for the replies and help. How could the code be made so that it would sum up the rows if the text is in proper text. For example instead of "YOU" "You".

 

[stefankemp]

Do you just want to count the cells that contain "MY" ?

If yes

=countif(K17:K100,"MY")

Put this in A1 if that's where you want to show it.

Change 100 to whatever is a suitable number for the length of your column.

The above will only count cells where the complete entry is "MY". If you want to count the number of cells that CONTAIN "MY", amend the formula to "=countif(K17:K100,"*MY*")"

 

[JackDanIce]

For that, change to:

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

 

[adamsm]

Thanks for all the help. I do really appreciate them.