Sum up rows with text

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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
 

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

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
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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:

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444

ADVERTISEMENT

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
Joined
Mar 11, 2010
Messages
136
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
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,842
Messages
5,708,919
Members
421,598
Latest member
NewHere

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?

Disable AdBlock

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
Top