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.

 

[bonnettc]

Figured it out using your Aladin's post along with posts on the first page. Basically
=SUMSIFS(c1:c5,B1:b5,"*"&"Dog"&"*")

So it only works with the proper capitalization, but that's okay for my use.

Thanks Aladin!

 

[Aladin Akyurek]

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

So the data is in A:B...

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.

Given A:B...

=SUMIFS(B:B,A:A,"*dog")

would also pick out a reddish dog if one is there in A.

Experiment also with...

=SUMIFS(B:B,A:A,"dog*")

and with...

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

 

[bonnettc]

Works perfectly. Thanks again!

 

[Aladin Akyurek]

Works perfectly. Thanks again!

You are welcome. Note that SUMIF(S) and kindred functions are case insensitive, that is, Dog, dog, DOG, etc. are all the same.

 

[ss3060]

I need to take the example of =COUNTIF(A1:A100,"WG") to the next level, so to speak. I have people's initials in column A. I want to count how many times WG shows up in Column A only if the cells next to it, in Column B, are not blank.

Thanks so much!

Sonya

 

[Rick Rothstein]

I need to take the example of =COUNTIF(A1:A100,"WG") to the next level, so to speak. I have people's initials in column A. I want to count how many times WG shows up in Column A only if the cells next to it, in Column B, are not blank.

Give this formula a try...

=SUMPRODUCT(--(A1:A100="WG"),--(B1:B100<>""))

 

[Aladin Akyurek]

I need to take the example of =COUNTIF(A1:A100,"WG") to the next level, so to speak. I have people's initials in column A. I want to count how many times WG shows up in Column A only if the cells next to it, in Column B, are not blank.

Thanks so much!

Sonya

Would this work for you?

=COUNTIFS(A1:A100,"WG",B1:B100,"<>")

You might even have:

=COUNTIFS(A:A,"WG",B:B,"<>")

If B is supposed to be text:

=COUNTIFS(A:A,"WG",B:B,"?*") _{^{<strike>
</strike>}}

 

[ss3060]

Give this formula a try...

=SUMPRODUCT(--(A1:A100="WG"),--(B1:B100<>""))

Thank you, Rick! Worked like a charm. Now, I need a refinement. In column P, I've got days, such as 3/1, 3/2, etc. I'd like to use this now formula to show me how many times 'WG' shows up according to the days in column P.

Many thanks, again!

 

[Aladin Akyurek]

Thank you, Rick! Worked like a charm. Now, I need a refinement. In column P, I've got days, such as 3/1, 3/2, etc. I'd like to use this now formula to show me how many times 'WG' shows up according to the days in column P.

Many thanks, again!

So you don't have COUNTIFS?

 

[Rick Rothstein]

Thank you, Rick! Worked like a charm.

You are welcome. Just wondering if you saw the formula Aladin posted in Message #27 (COUNTIFS, if your version of Excel has it, is probably more efficient than SUMPRODUCT)?

I'd like to use this now formula to show me how many times 'WG' shows up according to the days in column P.

I am not completely sure what you are asking for here. Can you give a more detailed description of what you are looking for?