Count # times a word is in a column but its not the only word in the cell

[tonywatsonhelp]

Hi Everyone,

I want a formula that can count the number of time "HI" is in columnB
but its with other words in some cells
so It might just be "Hi" or "Hi, High, Tell, Sell,"
I tryied this but it finds other words with HI in them and counts them as well
any ideas

=COUNTIFS(Courses!B:B,"*"&Statistics!C11&"*")

I've used countifs because there are other criterias I need to add.

Please help if you can

Tony

 

[jtakw]

Hi,

Adapt this to your needs:

Book1
	B	C	D
1	Hi	Hi	4
2	Hi, High, Tell, Sell
3	High, Tell, Sell
4	High, Tell, Hi, Sell
5	Tell, Sell, Hi
Sheet445

Cell Formulas
Range	Formula
D1	=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&C1&","," "&B1:B5&","))))

 

[Rick Rothstein]

One weakness of jtakw's formula is that it cannot count multiple occurrence of the search word within a single cell nor can it count the search word if it appears next to any character other than a space or comma (a period colon, semi-colon, dash, opening or closing parentheses, etc.). Here is a UDF (user defined function) that does not suffer these weaknesses. Its first argument is the word you want or text you want to count, its second argument is the cell or range of cells you want to look in and its optional third argument determines whether the search will be case sensitive or case non-sensitive (the latter being the default if this argument is omitted).

[table="width: 500"]
[tr]
	[td]Function CountExactWords(Word As String, Rng As Range, Optional CaseSensitive As Boolean) As Long
  Dim X As Long, Str1 As String, Str2 As String, Pattern As String, Cell As Range
  If CaseSensitive Then
    Str1 = Word
    Pattern = "[!0-9A-Za-z]"
  Else
    Str1 = UCase(Word)
    Pattern = "[!0-9A-Z]"
  End If
  For Each Cell In Rng
    If CaseSensitive Then
      Str2 = Cell.Value
    Else
      Str2 = UCase(Cell.Value)
    End If
    For X = 1 To Len(Str2) - Len(Str1) + 1
      If Mid(" " & Str2 & " ", X, Len(Str1) + 2) Like Pattern & Str1 & Pattern Then
        CountExactWords = CountExactWords + 1
      End If
    Next
  Next
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you 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 CountExactWords just like it was a built-in Excel function. For example (using jtakw's setup),

=CountExactWords(C1,B1:B5)

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.

 

[jtakw]

I've provided a working solution based on OP's sample data and description (thou limited), but thanks for pointing these out.

My formula also Won't work if there's No spaces and No delimiting characters of Any kind ( not just limited to commas ), like below:

HighTellHiSell

 

[tonywatsonhelp]

Thank you jtakw,
Your solution was very helpful, however I think it make sence to go with rick on this one as the function is great and easy to use.
Thanks big style to both of you.

Tony