COUNTIFS formula with double array criteria and exceptions

Question

I have COUNTIFS formula with double array criteria and multiple exceptions (.-/%#)

=SUM(COUNTIFS(D2;{"Txt1";"Txt2";"Txt3";"Txt4";"Txt5"}&" Nr."&{1\2\3\4\5\6\7\8\9}&"*";D2;"<>*.";D2;"<>*-";D2;"<>*/";D2;"<>*%";D2;"<>*#"))=0

And i want to optimize/combine all exceptions in one new array. Something like this:

=SUM(COUNTIFS(D2;{"Txt1";"Txt2";"Txt3";"Txt4";"Txt5"}&" Nr."&{1\2\3\4\5\6\7\8\9}&"*";D2;"<>*"&{".";"-";"/";"%";"#"}))=0

I studdy this explanation, but its too much over me.

P.S.

My version of excel for arrays use punctuation ; and \

Your excel version maybe use another combination like ; and , or , and .

Answer 1

I think you're misunderstanding the difference between how OR and AND statements are constructed within COUNTIFS.

For a given set of criteria, if you pass the elements of that set as an array to COUNTIFS, you are effectively performing an OR statement.

If, however, you pass each of those elements as a separate criteria parameter within COUNTIFS, you are effectively performing an AND statement.

So the two are not simply interchangeable for the sake of formula brevity.

For example:

=COUNTIFS(A1,"<>*A",A1,"<>*B",A1,"<>*C")

and

=SUM(COUNTIFS(A1,"*"&{"A";"B";"C"}))

are very different constructions.

The first is effectively checking whether:

The entry in A1 does not end with "A" AND the entry in A1 does not end with "B" AND the entry in A1 does not end with "C"

Whereas the second is effectively checking whether:

The entry in A1 does not end with "A" OR the entry in A1 does not end with "B" OR the entry in A1 does not end with "C"

With A1 containing "XYZ", for example

=COUNTIFS(A1,"<>*A",A1,"<>*B",A1,"<>*C")

will return 1, whereas

=SUM(COUNTIFS(D2,"<>*"&{"A";"B";"C"}))

will return 3.

There are also two other major issues with your intended abbreviation, which I will just summarize here:

1. OR criteria with the <> operator do not make logical sense
2. As XOR LX points out in that article you reference, unless you are 'pairing' elements within each set of (similarly dimensioned) array criteria, you cannot hope to create a construction which employs more than 2 sets of array criteria