CODEWITHSUNDEEP
excelexcel-formulacountif
chu
chu

Reputation: 3

In Excel, How to Exclude a list of Values in COUNTIF

This is giving me a huge number

=SUM(COUNTIF(A3:A777,{"<>*United Kingdom";"<>*France";"<>*United states";"<>*Germany";"<>*Switzerland";;"<>Estonia";"?"}))

Also, is there a more efficiënter way, If I want to change or add values the list in the future

Upvotes: 0

Views: 989

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

Best to switch to an MMULT construction when dealing with multiple 'not-equal-to' conditions:

=SUM(N(MMULT(N(ISNUMBER(SEARCH(TRANSPOSE(CountryList),A3:A777))),SEQUENCE(COUNTA(CountryList),,,0))=0))

where CountryList is a vertical range which comprises the list of values to exclude.

Upvotes: 2

Related Questions