Excel COUNTIFS引用整段范围排除多个值的公式写法咨询

你原来的公式返回0的核心问题是COUNTIFS不支持直接传入范围作为批量排除的条件：COUNTIFS的多条件逻辑为「同时满足所有给出的条件」，你直接写入"<>"&A2:A12时，公式只会读取范围的第一个值做判断，或者返回数组结果而没有做汇总，最终得到的结果自然不符合预期。

以下两种方案兼容性覆盖绝大多数Excel版本，运算效率也比逐行写10个排除条件更高。

方案1：总计数减列表内计数（逻辑最简单） #

先统计指定客户的所有条目总数，再减去该客户属于A2:A12列表内的版本总数，差值就是你要的排除后的数量：

=COUNTIF('BloombergVersionAnalysis (versi'!A:A,"ABG") - SUMPRODUCT(COUNTIFS('BloombergVersionAnalysis (versi'!A:A,"ABG",'BloombergVersionAnalysis (versi'!I:I,A2:A12)))

如果是Excel 2019及更早版本，输入完公式后按Ctrl+Shift+Enter触发数组运算即可生效。

方案2：MATCH匹配判断（适配复杂筛选场景） #

如果后续还有其他筛选条件要加，可以用SUMPRODUCT加MATCH的组合直接判断：

=SUMPRODUCT(('BloombergVersionAnalysis (versi'!A:A="ABG") * ISNA(MATCH('BloombergVersionAnalysis (versi'!I:I, A2:A12, 0))))

原理说明：

• 第一部分判断行是否属于ABG客户，符合返回1，不符合返回0
• 第二部分用MATCH匹配I列版本是否在A2:A12列表中，匹配不到返回#N/A，ISNA函数将不在列表中的行转为1，在列表中的转为0
• 两部分相乘后求和，得到的就是同时符合「是ABG客户」且「版本不在指定列表」的行数

你的数据集超过10000行，建议不要整列引用（比如A:A、I:I），改为实际数据的行范围（比如A1:A12000、I1:I12000），可以大幅提升公式运算速度。

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