COUNTIFS with array criteria and array exceptions-CodePudding

I need to COUNT 5 versions of (Begin with...) text in cell,

with 3 versions of (End with...) exception.

For this goal i write next formula:

=IF(SUM(COUNTIFS(A2,{"Begin1","Begin2","Begin3","Begin4","Begin5"}&".*Text*",A2,"<>"&{"*End1","*End2","*End3"}))=0,"NO","OK")

Unfortunately its working only with 3 texts and 3 exceptions.

How to make this formula working with 5 texts and 3 exceptions?

CodePudding user response：

You'r probably looking for something like:

=IF((SUM(COUNTIF(A2,{"Begin1","Begin2","Begin3","Begin4","Begin5"}&"*"))=1)*(SUM(COUNTIF(A1,"*"&{"End1","End2","End3"}))=0),"OK","NO")