Excel中如何用COUNTIF排除指定值？公式异常及优化需求

原公式结果异常大的原因 #

你用的=SUM(COUNTIF(A3:A777,{"<>*United Kingdom";"<>*France";"<>*United states";"<>*Germany";"<>*Switzerland";;"<>*Estonia";"?*"}))会重复统计单元格。举个例子：一个既不是英国也不是法国的单元格，会被<>*United Kingdom计数一次，又被<>*France计数一次，以此类推，同一个单元格会被多次累加，最终结果远大于实际符合条件的数量。

高效可维护的解决方案 #

把需要排除的国家放在单独的单元格区域（比如F1:F7，每个单元格放一个国家名称），后续修改/添加排除项直接编辑这个区域就行，不用改公式。

方案1：Excel 365/2021（支持动态数组） #

直接用总非空数减去匹配排除项的总数：

=COUNTA(A3:A777) - SUM(COUNTIF(A3:A777, "*"&F1:F7&"*"))

或者用XMATCH判断是否不在排除列表：

=SUMPRODUCT(--(NOT(ISNUMBER(XMATCH("*"&A3:A777&"*", "*"&F1:F7&"*")))))

方案2：旧版Excel（无动态数组支持） #

用SUMPRODUCT结合COUNTIF判断是否未匹配任何排除项：

=SUMPRODUCT(--(COUNTIF(F1:F7, "*"&A3:A777&"*")=0))

内容的提问来源于stack exchange，提问作者chu