# Sum up rows with text

##### Active Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try:
Code:
``````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``````

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.

In that case try:
Code:
``````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``````

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

If yes
Code:
``=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.

Alternatively, to make it a bit more generic, use:
Code:
``````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``````

Last edited:
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".

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

If yes
Code:
``=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*")"

For that, change to:
Code:
``````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``````

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

Replies
3
Views
109
Replies
4
Views
118
Replies
1
Views
178
Replies
3
Views
203
Replies
6
Views
247

1,219,578
Messages
6,149,098
Members
450,859
Latest member
njaitley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back