# 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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### JackDanIce

##### Well-known Member
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``````

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

#### JackDanIce

##### Well-known Member
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``````

#### Gerald Higgins

##### Well-known Member
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.

#### JackDanIce

##### Well-known Member
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:

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

##### Board Regular
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*")"

#### JackDanIce

##### Well-known Member
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``````

##### Active Member
Thanks for all the help. I do really appreciate them.

Replies
0
Views
143
Replies
4
Views
151
Replies
9
Views
181
Replies
1
Views
231
Replies
3
Views
92

### Forum statistics

1,176,221
Messages
5,901,985
Members
434,933
Latest member
danschlim ### 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.
Allow Ads at MrExcel

### Which adblocker are you using?    Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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