Count if multiple strings and words are found in 2 columns.

Hi All,

I have the following formula that should return the count of any text string in range A5:A8 that is found in column D and any of these words "East" or "South" or "West" are found in column E.
It is returning zero. Attached sample file.

=SUM(COUNTIFS($D:$D,"*"&$A$5:$A$8&"*",$E:$E,{"East";"South";"West"}))

Any idea how to make this formula to work ?

pls try this formula

Formula:

Please Login or Register  to view this content.

if you can live with a helper column, use this formula, say in K2, and copy down:

Formula:

Please Login or Register  to view this content.

Then your SUM will be like this:

Formula:

Please Login or Register  to view this content.

Good luck!

other option

Cell A2 array formula

Formula:

Please Login or Register  to view this content.

I see the semicolon was causing the problem in the original formula. All others formulas also works!

Thanks all for the help.

You’re welcome. Glad to help.
Thank you for the feedback and for the reputation added.
Have a blessed day.

@elbarauljr You're Welcome. Glad to help . Thank You for the feedback