worksheet function - 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.)

Answer

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:

   
   
   


2. 
   

   using CSE/array formula

   
   

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

   
   

   (more about Excel array formula)