CODEWITHSUNDEEP
excelexcel-formulacountif
Losai
Losai

Reputation: 329

COUNTIFS with array criteria and array exceptions

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?

Upvotes: 1

Views: 199

Answers (1)

JvdV
JvdV

Reputation: 75960

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

Upvotes: 2

Related Questions