Calc: count cells that contain a given string, but are not equal to it

edit

The definition of word is indeed ambiguous, as in the example singular and plural should be found. If it is only about sub strings then this "simple" approach would do, without the not neglectable overhead of regular expressions, assuming string to be found in cell A1 and data in B1:B4

=SUMPRODUCT(IFERROR(SEARCH(A1;B1:B4)>=1;0);LEN(B1:B4)>LEN(A1))

This would also find cat in concatenate of course.

Hello

your question is a bit ambiguous but I try an answer using an approach with regular expressions:

Assumption(s) for the example:

• For better readability the range of sentences is named: CLAIMS (e.g. CLAIMS is the range of cells A1:A4
• Since you consider various find strings, the pattern to be searched for is in a separate cell (in my example in B7
• The result will be into C7
• You want to find words Cat, cat, Cat, Cats, cat's, Cat's

then a (! - there might be others and smarter ones) solution may look like this:

Cell B7: \b[Cc]at[s’]{0,}\b (this is the regular expression)
Cell C7: =SUMPRODUCT(ISTEXT(REGEX(CLAIMS,B7,,"g"))*ISTEXT(REGEX(CLAIMS,B7,,"g")))

Hope that helps