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

Ask Your Question

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

asked 2017-05-05 19:11:20 +0200

inJesus gravatar image

updated 2021-06-06 11:37:48 +0200

Alex Kemp gravatar image

I would need a good formula for this for calc.

edit retag flag offensive 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.

Lupp gravatar imageLupp ( 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?

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

1 Answer

Sort by » oldest newest most voted

answered 2017-05-05 22:37:48 +0200

Lupp gravatar image

updated 2017-05-06 12:26:50 +0200

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?"

See also my answer to your question here.

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

edit flag offensive delete link more


Thank you ! Wow!

inJesus gravatar imageinJesus ( 2017-05-06 07:01:52 +0200 )edit

Question Tools

1 follower


Asked: 2017-05-05 19:11:20 +0200

Seen: 1,812 times

Last updated: May 06 '17