Trying to count instances of a word

[JohnB]

=COUNTIF(C2:C58,"MyWord")

I'm trying to count the number of instances of a particular word in a
column. I use the above formula, there are many instances, but the result
is zero.

What am I doing wrong? The format of the column is set to text.

Thanks

 

[Dave Peterson]

Is MyWord the only thing in the cell?

If yes, then make sure you're using the correct range.

If no,

anywhere in the cell:
=COUNTIF(C2:C58,"*MyWord*")

or at the start of the cell:
=COUNTIF(C2:C58,"MyWord*")

or at the end of the cell:
=COUNTIF(C2:C58,"*MyWord")

You can even put the word in another cell (like A1) and use a formula like:
=COUNTIF(C2:C58,"*"&a1&")

 

[JohnB]

It is not the only word.

Also, that may be my other problem; where I'm putting the formula. I have
it under the last cell in that column. Are you saying I have to have it in
each cell?

 

[Dave Peterson]

Nope, I'm not saying that.

But don't put the formula in your range (c2:c58 in your example).

Then go back to one of the cells that you _know_ contains that MyWord string
(and nothing more). You'll find that there's something else in it
(leading/trailing space or white space???).

Try retyping the word and you'll see the formula re-evaluates.

===============
One more thing to check...

Make sure you have calculation set to automatic.
Tools|Options|calculation tab
in xl2003 menus.

 

[JohnB]

Ok, I got it.

Of course I could have counted them manually in the amount of time it took
me to do this ;-)

But I have this knowledge for next time....
Thanks

 

[Niek Otten]

I've been trying to cope with more than one instance of the word in one cell
and with real "words", that is, surrounded by spaces, punctuation, brackets,
begin/end-of string, etc.
I'm not at all near to finishing that.
Does anyone have anything to give me a jump-start?

 

[Dave Peterson]

I don't have anything for you Niek.

But I think I've seen some posts where people who know regular expressions have
taken a whack at it using a UDF.

Me...I'd just try to come up with a list of characters that can be surrounding
that word and change them to spaces, then look for the word surrounded by
spaces.

or even look the valid characters -- say they could only be a-z, then you could
do the same kind of thing in reverse. If it's not a-z, then change it to a
space.

Option Explicit
Function myCountif(rng As Range, myWord As String) As Long

Dim myCell As Range
Dim myStr As String
Dim iCtr As Long
Dim myTotal As Long

myTotal = 0
For Each myCell In rng.Cells
myStr = LCase(myCell.Value)
For iCtr = 1 To Len(myStr)
If Mid(myStr, iCtr, 1) Like "[a-z]" Then
'keep it
Else
Mid(myStr, iCtr, 1) = " "
End If
Next iCtr
myStr = " " & myStr & " "
If InStr(1, myStr, " " & myWord & " ", vbTextCompare) Then
myTotal = myTotal + 1
End If
Next myCell

myCountif = myTotal

End Function

I've been trying to cope with more than one instance of the word in one cell
and with real "words", that is, surrounded by spaces, punctuation, brackets,
begin/end-of string, etc.
I'm not at all near to finishing that.
Does anyone have anything to give me a jump-start?

 

[JP]

What about the CSE formula

=SUM(LEN(MyRange)-LEN(SUBSTITUTE(MyRange,"WordToFind","")))/LEN
("WordToFind")

or am I misunderstanding?

--JP

 

[Ron Rosenfeld]

I've been trying to cope with more than one instance of the word in one cell
and with real "words", that is, surrounded by spaces, punctuation, brackets,
begin/end-of string, etc.
I'm not at all near to finishing that.
Does anyone have anything to give me a jump-start?

=============================
Option Explicit
Function WordCount(SearchIn As String, SearchFor As String) As Long
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.ignorecase = True 'or false, depending
.Pattern = "\b" & SearchFor & "\b"
End With
Set mc = re.Execute(SearchIn)
WordCount = mc.Count
End Function
===============================

And the SearchFor can be a word, or a phrase.
--ron

 

[Ron Rosenfeld]

What about the CSE formula

=SUM(LEN(MyRange)-LEN(SUBSTITUTE(MyRange,"WordToFind","")))/LEN
("WordToFind")

or am I misunderstanding?

--JP

That doesn't return a count of "real words" as I understand Niek to mean. In
other words, if the phrase is

"Orange range"

and the word to find is "range", your formula returns a count of 2, where Niek
would want a count of 1
--ron

 

[anandydr]

Supposing you have data in following format:

Br

SAP

Br, SAP

Comp

Br, Gov

Br, SAP

Comp

Br

SAP

Any of the following formula will count the number of instances "Br"
occurs within text.

=COUNTIF($A$1:$A$10,"=*Br*")
=SUMPRODUCT((LEFT($A$1:$A$10,2)="Br")*(1))
=SUMPRODUCT(1-ISERROR(SEARCH("br",$A$1:$A$10)))
=SUMPRODUCT(--NOT(ISERROR(SEARCH("br",$A$1:$A$10))))

 

[Ron Rosenfeld]

Supposing you have data in following format:

Br

SAP

Br, SAP

Comp

Br, Gov

Br, SAP

Comp

Br

SAP

Any of the following formula will count the number of instances "Br"
occurs within text.

=COUNTIF($A$1:$A$10,"=*Br*")
=SUMPRODUCT((LEFT($A$1:$A$10,2)="Br")*(1))
=SUMPRODUCT(1-ISERROR(SEARCH("br",$A$1:$A$10)))
=SUMPRODUCT(--NOT(ISERROR(SEARCH("br",$A$1:$A$10))))

Niek's request, in part:

"I've been trying to cope with more than one instance of the word in one cell "

All of your formulas return a count of "1" with the following:

Br SAP Br

and I believe Niek would want a count of 2.
--ron

 

[Ron Rosenfeld]

=============================
Option Explicit
Function WordCount(SearchIn As String, SearchFor As String) As Long
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.ignorecase = True 'or false, depending
.Pattern = "\b" & SearchFor & "\b"
End With
Set mc = re.Execute(SearchIn)
WordCount = mc.Count
End Function
===============================

And the SearchFor can be a word, or a phrase.
--ron

Of course, you can also loop through a range to get a total, if you need to
check more than one cell.
--ron