Need to count number of occurrences of a word in a column

jase

Board Regular
Joined
Nov 4, 2002
Messages
62
Column A has 100 entries (A2 through A101)
Some of these entries are "Houston"

What formula can I use to calc. how many times the word "Houston" appears within that Column so that I don't have to manually count each time???

Thanks for any help.
jase.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Geezum's -- you'd think I would have gotten that. I was close. Thanks a million.

jase.
 
Upvote 0
I am also need to count the number of occurences of a particular word across various cells and columns in Excel, however in my case cells don't contain a single word but a large amount of text.

:confused:
 
Upvote 0
I am also need to count the number of occurences of a particular word across various cells and columns in Excel, however in my case cells don't contain a single word but a large amount of text.

:confused:

Try...

H2: manhattan

I2:

=COUNTIF(A1:F100,"*"&H2&"*")
 
Upvote 0
I am also need to count the number of occurences of a particular word across various cells and columns in Excel, however in my case cells don't contain a single word but a large amount of text.

:confused:
Similarly I have a bunch of text in many cells of a sheet and want to count occurrences of words, but if a cell contains "with" three times I want the countrer for with to go up by three, countif will only count one per cell?

Thanks
 
Upvote 0
Try array formula, confirm with Ctrl+Shift+Enter:
Code:
=(SUM(LEN(A1:F100))-SUM(LEN(SUBSTITUTE(SUBSTITUTE(A1:F100,"with",""),"With",""))))/LEN("with")

As this is case sensitive, I included both "with" and "With".
 
Upvote 0
Similarly I have a bunch of text in many cells of a sheet and want to count occurrences of words, but if a cell contains "with" three times I want the countrer for with to go up by three, countif will only count one per cell?

Thanks

This excludes bits like without when looking for with...

=SUMPRODUCT(LEN(" "&A1:F100&" ")-LEN(SUBSTITUTE(UPPER(" "&A1:F100&" ")," WITH ","")))/LEN(" with ")
 
Upvote 0
Try array formula, confirm with Ctrl+Shift+Enter:
Code:
=(SUM(LEN(A1:F100))-SUM(LEN(SUBSTITUTE(SUBSTITUTE(A1:F100,"with",""),"With",""))))/LEN("with")

As this is case sensitive, I included both "with" and "With".

Thanks Marcel, that's perfect! Thanks

Interesting idea Aladin, but it will fail to pick up words followed by comma's and other punctuation and for this it's easier to minus the count of withouts from the withs... (as it is I'm picking out certain words and I'm happy for Repair to find repairs and repaired...)

Thanks for the help both.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top