Counting words (text strings) in a range

[JenniferMurphy]

I know CountIf can count the number of occurrences of a word in a range if each call in that range contains only a single word. Is there a formula that can count the number of occurrences of a word in a range where each cell may contains multiple words?

In this table, I would like the "house" count to be "3".
[TABLE="class: grid, width: 480"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]house[/TD]
[TD="align: center"]1[/TD]
[TD]D4: =COUNTIF($C$4:$C$7,"house")[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]dog[/TD]
[TD="align: center"]1[/TD]
[TD]D5: =COUNTIF($C$4:$C$7,"dog")[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]house dog[/TD]
[TD="align: center"]0[/TD]
[TD]D6: =COUNTIF($C$4:$C$7,"cat")[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]cat house
[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[sheetspread]

You can use a wildcard character or sumproduct with search/find

 

[JenniferMurphy]

You can use a wildcard character or sumproduct with search/find

I don't understand the sumproduct solution, but the wildcard solution does just what I wanted.
[TABLE="class: grid, width: 525"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]house[/TD]
[TD]house[/TD]
[TD="align: center"]2[/TD]
[TD]E4: =COUNTIF($C$4:$C$7,"*" & $D4 & "*")[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]dog[/TD]
[TD]dog[/TD]
[TD="align: center"]3[/TD]
[TD]E5: =COUNTIF($C$4:$C$7,"*" & $D5 & "*")[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]house dog[/TD]
[TD]cat[/TD]
[TD="align: center"]1[/TD]
[TD]E6: =COUNTIF($C$4:$C$7,"*" & $D6 & "*")[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]dog cat[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you

-j

 

[Peter_SSs]

.. the wildcard solution does just what I wanted.
E6: =COUNTIF($C$4:$C$7,"*" & $D6 & "*")

So long as you are happy to have, for example, "cat" counted in a cell that contains "Tom was scathing"

 

[JenniferMurphy]

So long as you are happy to have, for example, "cat" counted in a cell that contains "Tom was scathing"

Sigh. There is always a catch.

For my immediate needs, it's fine. I am making a large family photo collage. For each photo that I add to the collage, I add the list of names of people in the photo, so I keep it more or less "even". I don't want to have Aunt Edna in 25 photos and Aunt Irma in only 5. All of the names are unique and we don't have any nested names (Bill & Billy).

But suppose I wanted to only count complete words (so not count "Bill" in "Billy"). How would you do that?

 

[Aladin Akyurek]

Try:

=SUMPRODUCT(ISNUMBER(SEARCH(" "&D4&" "," "&$C$4:$C$7&" "))+0)

 

[Rick Rothstein]

Try:

=SUMPRODUCT(ISNUMBER(SEARCH(" "&D4&" "," "&$C$4:$C$7&" "))+0)

The above formula only works if each word (name) being looked for is surrounded by spaces... it will not pick up words (names) adjacent to non-letters such as in text like these...

Bob, Bill, Joe and Sally

A beautiful view (Bill and Joe on the rock, Bob leaning on the tree)

I have not tested this fully, but I believe the following formula will work no matter what non-alpha character surrounds the word in cell D4.

=SUMPRODUCT((NOT(ISNUMBER(FIND(UPPER(MID("="&$C$4:$C$7&"=",SEARCH(D4,$C$4:$C$7),1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))*NOT(ISNUMBER(FIND(UPPER(MID("="&$C$4:$C$7&"=",SEARCH(D4,$C$4:$C$7)+LEN(D4)+1,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))))

 

[Peter_SSs]

But suppose I wanted to only count complete words (so not count "Bill" in "Billy"). How would you do that?

If there is no punctuation, then Aladin has provided a solution.

If there is punctuation & you want the solution to be robust, then with great difficult if not impossibility. Excel is much more suited to dealing with numbers than words. Rick's suggestion seems to have something drastically wrong.

Excel Workbook
	C	D	E
4	Mary	Jim	4
5	Bob, Bill, Joe and Sally	Bill	3
6	Sam Billy	Mary	4
7	Ken
Sheet1

 

[Rick Rothstein]

If there is punctuation & you want the solution to be robust, then with great difficult if not impossibility. Excel is much more suited to dealing with numbers than words. Rick's suggestion seems to have something drastically wrong.

Wow, I really screwed that up, didn't I? I am not entirely sure what happened as the formula I developed (which I am guessing was not the one I ended up posting) seemed to work in my tests. Unfortunately, the worksheet it was on is long gone, so I cannot check if I copied it correctly or not. Anyway, let me stick with code (which I am far more comfortable with than formulas) and post this UDF which I believe will return the correct count of cells where adjacent non-alpha characters do not affect the count. The UDF takes two mandatory arguments... the first is the text to search for and the second is the range to search for it in... and one optional argument... which allows the search to be case sensitive or not (default is for an insensitive search).

[table="width: 500"]
[tr]
	[td]Function CountWholeWords(FindMe As String, RangeToSearch As Range, _
                         Optional CaseSensitive As Boolean) As Variant
  Dim X As Long, CV As String, FM As String, Pattern As String, Cell As Range
  If Len(FindMe) = 0 Then
    CountWholeWords = ""
    Exit Function
  End If
  For Each Cell In Intersect(RangeToSearch, RangeToSearch.Parent.UsedRange)
    If Len(Cell.Value) Then
      If CaseSensitive Then
        CV = Cell.Value
        FM = FindMe
        Pattern = "[!0-9A-Za-z]"
      Else
        CV = UCase(Cell.Value)
        FM = UCase(FindMe)
        Pattern = "[!0-9A-Z]"
      End If
      For X = 1 To Len(CV) - Len(FM) + 1
        If Mid(" " & CV & " ", X, Len(FM) + 2) Like Pattern & FM & Pattern Then
          CountWholeWords = CountWholeWords + 1
        End If
      Next
    End If
  Next
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If the OP (or readers of this thread) are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CountWholeWords just like it was a built-in Excel function. For example,

=CountWholeWords(D4,$C$4:$C$7)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Peter_SSs]

.. this UDF which I believe will return the correct count of cells...

I'm still not so sure - ref count for Mary below with your function in column E.

I have suggested another UDF, used in column F, though I wouldn't be surprised if examples can be found where it fails too.
My function assumes a single column range to be searched as per the OP's original sample.

Function CountWord(rng As Range, wrd As String) As Long
  With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = True
    .Pattern = "@.*?\b" & Replace(Replace(wrd, "-", "#"), "'", "%") & "(?=[ ,\.\?\!:;@]|$)"
    CountWord = .Execute("@" & Replace(Replace(Join(Application.Transpose(rng), "@"), "-", "#"), "'", "%")).Count
  End With
End Function

Excel Workbook
	C	D	E	F
4	Mary Sue Bill.	Mary-Lou	1	1
5	Bob, Bill, Joe, Billy and Sally	Mary	3	2
6	Mary-Lou, Ken, Mary	Bill	2	2
7		Sam	0	0
Sheet2