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

[jase]

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.

 

[MarcelBeug]

You're welcome.

 

[Aladin Akyurek]

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.

If so, why don't you try...

=SUMPRODUCT(LEN(A1:F100)-LEN(SUBSTITUTE(UPPER(A1:F100),"WITH","")))/LEN("with")

which is faster?

 

[Khornight]

I wasn't going to make the changes you suggested, because the answer from Marcel was working well enough, but I had to change something else so figured I'd do it and it's noticeably faster. Cheers.

 

[Aladin Akyurek]

I wasn't going to make the changes you suggested, because the answer from Marcel was working well enough, but I had to change something else so figured I'd do it and it's noticeably faster. Cheers.

I'm sure Marcel won't mind. Thanks for the update.

 

[István Hirsch]

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.

The formula in post #8 counts 2 for "with or without". Is this what you need?

 

[MarcelBeug]

I'm sure Marcel won't mind.

That's right, Aladin.

 

[bonnettc]

The tips posted work, but I need to complicate the question. Is there a formula that will count the occurrence of a value in a column based on the value of another column?

For example, consider this data
red dog 1
red cat 0
blue dog 0
red cat 1
green dog 3

I need a formula that would search for "dog", then if found sum the value in the second column. In the example, if searching for "dog", I would get 4 (1+0+3).
Any ideas? Thanks!

 

[bonnettc]

Sorry, my formatting is bad, there was intended to be two distinct columns, column A with red dog...green dog, and column B with the numbers. My formatting is crap

 

[Aladin Akyurek]

Let's A:C house the sample you posted.

Try...

=SUMIFS(C:C,B:B,"dog")

 

[bonnettc]

Let's A:C house the sample you posted.

Try...

=SUMIFS(C:C,B:B,"dog")

Thanks, I played with that command but only works with exact matches. with the columns having 'red dog', 'blue dog', etc., this doesn't work. Any other ideas?
Thanks again.