How to count the number of cells in a range containing a specific (sub)string with Excel/Calc?

Using Excel/Calc I have the following situation:

• a range of cells containing values; assume it's A1:B10,
• and a string I want to look for; for example, assume it is contained in C1.

Now what I want to do is count how many cells in the range equal that string or contain it as a sub-string.  How do I get that?  (For example, assume that I want the result in D1.)

Solution 1:

I answer myself as I do this at least once a year and I feel the need of having a place to look for quickly when I forget something, plus I think this could be useful for someone else too.

So, ok, I know of two ways to do that:

1. using conventional formula

   in D1 put =COUNTIF(A1:B10,"*"&C1&"*")

   Some notes (mostly) for newbies:

   • those "*" in the formula are Excel wildcards (a list of Excel functions that can use wildcards)

   • those "&" are a way to concatenate text without using CONCATENATE (more on this here or here too )

2. using CSE/array formula

   D1 should be {=SUM(IF(ISNUMBER(SEARCH(C1,A1:B10)),1,0))}

   (more about Excel array formula)