Counting the number of times a string appears within a range of values

[alon]

Hello all,

Before I start describing my problem, I have uploaded a screenshot of my worksheet to tinypic.com. You can see it here:

http://oi51.tinypic.com/2j34ca9.jpg

and also, here's the worksheet itself:
http://gopile.us/hlc

What I'm trying to calculate is in how many of the values - the cells - in column A "username", the string in D2 appears. I'm not looking for an exact match, so if D2 = "martin", than any cell in column A that has "martin" in it, such as "xxxmartinyyy", would count.

In the sheet and the screenshot I describe what I tried to do.
In column C I calculated for each row if the term in D2 appears in the cell in column A. Hence, I know that there's a total of 14 values. However, I'm not interested in doing this in such a complicated and long way,
and this is why I turn to you.

Please help.

Thank you!

 

[mancemonster]

Hi!

Welcome to the boards...

Would ....

=COUNTIF(A:A,"*"&D2&"*")

Do the trick?


Regards

Roger

 

[VoG]

Try

=COUNTIF(A:A,"*"&D2&"*")

 

[alon]

Yes, it works. So simple! Can you perhaps explain why the formula I used doesn't work? It holds until the "countif" argument. Thanks again