We will be migrating from Ask to Discourse on the first week of August, read the details here

# How to find out if a search string is found as (part of a) content in a range of cells? [closed]

I would need a good formula for this for calc.

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-10-03 12:31:14.974623

My answer assumes the "countif" above is substantial. Please try to be clear about such things (and mention the most relevant keywords in the subject).

Interpreting the question in a different way: See my answer to "the other" question linked in below.

( 2017-05-05 22:53:28 +0200 )edit

You are right. I need to be more clear and open. I am just searching for a good way to do this.

Would you suggest any more tags to add?

( 2017-05-06 06:57:32 +0200 )edit

Sort by » oldest newest most voted

COUNTIF is made to count cells matching a condition given the 'Criteria' way. It is not made basically to find out if a condition comes out true.
Therefore I assume the question is expected to be read as:
"Can I count the cells within a range based on the condition that they are containing a specific string as a part of their contents or of the results returned to them by formulae?"

Yes. The ways to do so partly depend on settings. To get it without avoidable complications I only give solutions here that are independent of these settings. You may ask for more detail if neded. Often a solution based on SUMPRODUCT is preferable.

A) Case sensitive
{=COUNTIF(ISNUMBER(FIND(SearchString; Range));TRUE())} must be entered for array evaluation.
=SUMPRODUCT(ISNUMBER(FIND(SearchString; Range))) is evaluated in array-mode anyway.

B) Case insensitive
{=COUNTIF(ISNUMBER(FIND(UPPER(SearchString); UPPER(Range)));TRUE())} must be entered for array evaluation.
=SUMPRODUCT(ISNUMBER(FIND(UPPER(SearchString); UPPER(Range)))) is evaluated in array-mode anyway.

(Editing with respect to the first comment by the OQ below:)

Having ticked 'Enable regular expressions in formulae' and accepting case insensitive evaluation COUNTIF can also be used without transforming the 'Range' under array-evaluation.
=COUNTIF(Range;RegExSearchString)
with RegExSearchString = "^.*"&SearchString&.*$" . In the case visualised in the fakepath picture the formula should be =COUNTIF(F6:G6;"^.*"&H$1&.*$") then. Please be sure to understand that (e.g.) with PrO in H$1 the formula will also count a cell containing There was an uproar in the country. in F6:G6.
If a different behaviour is aspired I need to know all the details to be able to advise.

more