带唯一值的复杂COUNTIFS公式问题续：公式突然失效求助

问题背景 #

需统计符合以下条件的员工数量：

• A列值为FORWARD
• 工时（M列）大于0，排除未工作员工
• 同一员工（I列）当日任职多岗位时不重复计数

此前使用的公式（由Mayukh Bhattacharya提供）现在在J1单元格返回0，无法得到正确结果：

=SUMPRODUCT(IFERROR((("FORWARD"=$A3:$A$28)*($M$3:$M$28>0))/
  COUNTIFS($A3:$A$28,$A3:$A$28,$I$3:$I$28,$I$3:$I$28,$M$3:$M$28,">0"),0))

排查步骤 #

• 确认数据范围匹配：检查公式中的$A3:$A$28、$M$3:$M$28、$I$3:$I$28是否覆盖全部有效数据行，若数据有新增/删减，范围需同步调整
• 检查文本匹配精度：核实A列的FORWARD是否存在大小写差异、前置/后置空格或不可见字符，这类问题会导致等式匹配失败
• 测试COUNTIFS分母：单独提取COUNTIFS($A3:$A$28,$A3:$A$28,$I$3:$I$28,$I$3:$I$28,$M$3:$M$28,">0")部分，查看是否有返回0的情况（分母为0会被IFERROR转为0，拉低总和）
• 验证M列格式：确保M列是数值格式，若为文本格式，>0的条件判断会失效
• 检查筛选/隐藏行：若数据应用了筛选或手动隐藏行，部分Excel版本中SUMPRODUCT会忽略隐藏行，导致统计范围缩小

修正方案 #

方案1（Excel 365/2021及以上版本，推荐） #

使用动态数组公式，逻辑更直观且不易出错：

=COUNTA(UNIQUE(FILTER($I$3:$I$28,($A$3:$A$28="FORWARD")*($M$3:$M$28>0))))

逻辑说明：

1. FILTER筛选出A列=FORWARD且M列>0的员工ID
2. UNIQUE对筛选结果去重，避免同一员工多岗位重复计数
3. COUNTA统计去重后的有效员工数量

方案2（兼容旧版Excel） #

修正原公式的引用方式，将混合引用改为全绝对引用（确保公式在J1时统计范围固定）：

=SUMPRODUCT(IFERROR((($A$3:$A$28="FORWARD")*($M$3:$M$28>0))/COUNTIFS($A$3:$A$28,$A$3:$A$28,$I$3:$I$28,$I$3:$I$28,$M$3:$M$28,">0"),0))

原公式中$A3:$A$28为混合引用（行3相对），若公式在J1，可能因引用偏移导致范围错误，改为全绝对引用后固定统计区域。

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