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


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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.