0

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.

1
  • 2
    What about =COUNTA(A:A)-COUNTIFS(...)?
    – JohnSUN
    Mar 3, 2023 at 11:08

1 Answer 1

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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.