Two search conditions in an array formula.

[saracz66]

I am using the following array formula to count the occurrances of a unique string:

{=SUM(IF(ISNUMBER(FIND("search string",Data!B:B,1)),1,0))}

My worksheet contains the following sample data:

ColA ColB
Yes this is the search item
Yes this is also the search string
Yes this is another search string
No this is a 4th search string

My formula above renders a count of 3, since "search string" is in column B on three of the four rows.

I would like to expand the formula to include an additional requirement where "Yes" is also found in column A. So after the formula modification, I would expect to get a count of 2 since "Yes" is on only two of the three rows containing "search string".

 

[saracz66]

Just figured it out on my own! Simply add another IF condition. For example:

=SUM(IF(ISNUMBER(FIND("search string",Data!B:B,1)),IF(ISNUMBER(FIND("Yes",Data!A:A,1)),1,0),0))