text search in multi text cells

edit

Formula in F2 =SUMPRODUCT(IF(ISNUMBER(SEARCH("bill";B2:E9))))

C:\fakepath\Untitled 2.ods

EDIT 2020-10-26 08:40:00

This will also count compoundwords like "hillbilly".

Then a correction to the formula : =SUMPRODUCT(IF(ISNUMBER(SEARCH("\<bill\>";B2:E9))))

or a new formula: =COUNTIF(B2:E9;".*\<bill\>.*")

Hello @luber1

I try a proposal : use COUNTIF with a regular expression, like =COUNTIF(A3:A12;"=.*(B|b)ill.*") if the source data are in range A3:A12

.* looks for any number of any kind of characters

(B|b) allows either choice between B or b

Allow for regular expressions to be used in formulas by Alt+F12>LibreOffice Calc> Calculate> Enable regular expressions in formulas

Complement to my answer

Based on my own confusion between COUNTA and COUNTIF as, in French, they are translated respectively NBVAL and NB.SI, which do not look exactly the same but close by, @luber1, if your working language is not English, in order to catch the right function name in your own language, transit temporarily by English. Close all other LO documents. Open a new blank spreadsheet. Activate the option Alt+F12>LibreOffice Calc> Formula>Use English function names>OK. Then type in a set of data and two formulas, one with COUNTA(...) and the other one with COUNTIF(...). Save the spreadsheet. De-activate the option for Use of English function names. Re-open the spreadsheet and compare the names of both functions in you own language.

Hope this helps too.

To show the community that the question has found its answer, please click on the ✓ aside the top of the correct answer and vote by clicking the ^ caret of all usefull answer.

Please do not use Add Answer but edit your original question to enhance the details of your question (answers are reserved for solutions to a problem on this Q&A site). Thanks in advance …

Kind regards, Michel

RegEx are a very powerful means for searching and manipulating strings. Concerning the usage in Calc this power was significantly improved by the implementation of the REGEX() function since version 6.2.
You may learn about ways to use RegEx for purposes like addressed by the question in a playful way using the attached spreadsheet. C:\fakepath\ask271841FindAndCountStringsInCellTexts.ods

The Regex engine used by LibreOffice is the one by ICU.
A very recommendable site concerning RegEx is (imo) https://www.regular-expressions.info/....

Just 4fun,

here is a macro function solution. Just place it into Tools > Macros ... > Edit Macros and you can call it like any other calc function via:

=CMIR("Bill",B2:D10)

• 1st argument is the string to search for (case insensitiv)
• 2nd is a selected cell range
• Return value is the count of found matches

Here the macro function code:

rem (c)ount (m)atches (i)n  (r)ange
function CMIR(needle as string, data() as array) as integer
    count=0
    for i = lbound(data,1)to ubound(data,1) 
        for j = lbound(data,2)to ubound(data,2)
            parts() = Split(lcase(data(i,j)), lcase(needle))
            blub = UBound(parts()) - LBound(parts())
            if ( blub > 0 ) then
                count=count+blub
            endif
        next 
    next 
    CMIR=count
end function

--- update ---

As promised, here is the regex version (2v1) had to fix some bugs sorry

usage:

=CMIR2("\b[B|b][Ii][Ll][Ll]\b",B2:D9)

code:

rem (c)ount (m)atches (i)n (r)ange version2v1
function CMIR2(needle as string, data() as array) as integer
  Dim oTextSearch               ' TextSearch service.
  Dim sStrToSearch As String    ' String to search.
  Dim aSearchResult             ' com.sun.star.util.SearchResult
  Dim aSrcOpt As New com.sun.star.util.SearchOptions
  Dim enLocale As New com.sun.star.lang.Locale
  Dim rank As Long
  Dim count as long
  enLocale.Language = "en"
  enLocale.Country = "US"
  oTextSearch = CreateUnoService("com.sun.star.util.TextSearch")
  With aSrcOpt
    'https://api.libreoffice.org/docs/idl/ref/SearchFlags_8idl.html
    .searchFlag = com.sun.star.util.SearchFlags.REG_EXTENDED
    .Locale = enLocale
    'Supports ABSOLUTE, REGEXP, and APPROXIMATE
    .algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
    .searchString = needle 'rem define word bounds
    '.transliterateFlags = com.sun.star.i18n.TransliterationModulesNew.UPPERCASE_LOWERCASE
  End With
    oTextSearch.setOptions(aSrcOpt)
    count=0
    rank = 0
    for i = lbound(data,1)to ubound(data,1) 
        for j = lbound(data,2)to ubound(data,2)
              sStrToSearch = data(i,j)
               aSearchResult = oTextSearch.searchForward(sStrToSearch, 0, Len(sStrToSearch)  )
                rank=0
                    Do While aSearchResult.subRegExpressions > 0
                        count=count+1
                        sStrToSearch = mid(sStrToSearch, aSearchResult.endOffset(0) +1 , Len(sStrToSearch))
                        aSearchResult = oTextSearch.searchForward(sStrToSearch, 0, Len(sStrToSearch)  )                     
                  Loop
        next 
    next 
    CMIR2=count
end function

This Function can handel the hillbilly problem very gracefully. By using word boundarys \b

Hope it helps (even more)