Count the number of occurrences where a cell value does NOT contain a value from a pre-defined array

Question

I want to count the number of occurrences where a cell value does NOT contain a value from a pre-defined array.

Audi
BMW
Audi
Mercedes
Ford
BMW

Ford

=COUNTIFS(A:A,NOT({"","Audi","BMW"}))

So with my data the expected result would be 3. I'm using COUNTIFS because there will be multiple checks in my final formula.

I checked here, but that returns true/false for each item, which I don't need.

Answer 1

Use filter() to get the rows that match your criteria, iferror() to weed out a possible #N/A error, and counta() to get the number of items that match said criteria, like this:

=counta( iferror( filter(A1:A, A1:A <> "", A1:A <> "Audi", A1:A <> "BMW") ) )

See filter().