Trying to count instances of a word

  • Thread starter Thread starter JohnB
  • Start date Start date
J

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
 
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&")
 
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?
 
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.
 
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
 
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?
 
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

Niek said:
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?
Click to expand...
 
What about the CSE formula

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

or am I misunderstanding?

--JP
 
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?
Click to expand...


=============================
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
 
What about the CSE formula

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

or am I misunderstanding?

--JP
Click to expand...

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
 
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))))
 
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))))
Click to expand...

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
 
=============================
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
Click to expand...

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