相同公式在不同单元格返回不同结果的技术求助

问题核心 #

使用以下公式统计数据集1元素大于数据集2对应元素的数量时，在“Data_set2”工作表的K57:AJ108区域出现异常：部分本该返回100%（100个数据全符合条件）的单元格显示0%，仅差异是后续除以总数据点数。
原公式：

=COUNTIF(ARRAYFORMULA(ABS(FILTER(INDIRECT($X3); ISNUMBER(INDIRECT($X3))))); "<" & INDIRECT(CONCAT("AnotherSheet!";$W3)))

已排查格式问题、引用正确性、公式复制方式，移除部分INDIRECT引用后问题仍存在。

原因分析 #

1. COUNTIF参数逻辑不匹配需求：COUNTIF的第二参数仅支持单一条件值，当INDIRECT(CONCAT("AnotherSheet!",$W3))返回数组时，公式只会取数组的第一个元素拼接成条件，导致所有数据集1的元素都和这个单一值对比，而非对应位置的数据集2元素。如果数据集2的第一个元素远大于数据集1的所有元素，就会出现统计结果为0的情况，哪怕其他位置的元素都符合条件。
2. ARRAYFORMULA与COUNTIF兼容性问题：COUNTIF本身是对整个数组统计单一条件的数量，而你需要的是逐元素配对对比后统计符合条件的数量，原公式逻辑本质上不匹配需求，只是在部分巧合场景（比如数据集2所有元素等于第一个元素）下表现正常。

解决方案 #

替换原公式为逐元素对比后求和的逻辑，推荐使用SUMPRODUCT函数：

=SUMPRODUCT(--(ARRAYFORMULA(ABS(FILTER(INDIRECT($X3), ISNUMBER(INDIRECT($X3))))) < INDIRECT(CONCAT("AnotherSheet!", $W3))))

公式说明 #

• ARRAYFORMULA(ABS(FILTER(...))) 处理并提取数据集1的有效数值数组
• < INDIRECT(...) 实现两个数组的逐元素对比，返回布尔值数组（TRUE/FALSE）
• -- 将布尔值转换为1/0，方便求和
• SUMPRODUCT 对转换后的数值数组求和，得到符合条件的元素数量

优化版公式（减少重复计算） #

用LET函数缓存引用的数组，提升计算效率并降低出错概率：

=LET(
  data1, ARRAYFORMULA(ABS(FILTER(INDIRECT($X3), ISNUMBER(INDIRECT($X3))))),
  data2, INDIRECT(CONCAT("AnotherSheet!", $W3)),
  SUMPRODUCT(--(data1 < data2))
)

额外检查点 #

确认INDIRECT($X3)和INDIRECT(CONCAT("AnotherSheet!", $W3))返回的数组长度一致，若长度不同，SUMPRODUCT会按最短数组的长度计算，可能导致结果偏差。

内容的提问来源于stack exchange，提问作者I Have No Idea What I am Doing