I would need a good formula for this for calc.
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.
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?
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.
=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.
Thank you ! Wow!