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

Thanks:  0
Likes:  0

# Thread: Need to count number of occurrences of a word in a column

1. ## Need to count number of occurrences of a word in a column

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.

2. =COUNTIF(A1:A100,"Houston")

3. Geezum's -- you'd think I would have gotten that. I was close. Thanks a million.

jase.

4. ## Re: Need to count number of occurrences of a word in a column

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.

5. ## Re: Need to count number of occurrences of a word in a column

Originally Posted by Juan Pablo González
=COUNTIF(A1:A100,"Houston")
Cool It really helped.

6. ## Re: Need to count number of occurrences of a word in a column

Originally Posted by Cherique
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.

Try...

H2: manhattan

I2:

=COUNTIF(A1:F100,"*"&H2&"*")

7. ## Re: Need to count number of occurrences of a word in a column

Originally Posted by Cherique
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.

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

8. ## Re: Need to count number of occurrences of a word in a column

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".

9. ## Re: Need to count number of occurrences of a word in a column

Originally Posted by Khornight
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 ")

10. ## Re: Need to count number of occurrences of a word in a column

Originally Posted by MarcelBeug
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•