语境：我正在处理 Excel 2010 电子表格，汇总大量数据并通过比较几列的内容得出各种结果。所有细节都不重要；我将提供一个简化的实际问题示例。
我有基于使用 SUMPRODUCT 的公式。我相信我应该能够使用基于 COUNTIFS 的公式获得相同的结果。但是，当我尝试时，COUNTIFS 公式不会产生正确的结果，我试图了解这是为什么。以下是一个例子：
源数据位于 A、B 和 C 列。我根据标记为“活跃天数”的 A 列进行总结。
E 列是不同活跃天数的列表，范围从 1 到 31。F 列到 H 列中的值是与每个活跃天数相关的摘要数字。该示例是从大型数据集中提取的，因此并非每个活跃天数都有数据显示。
F 列只是具有该活跃天数的记录数。
G 列和 H 列是派生结果的一个示例。G 列使用我的 COUNTIFS 公式计算，H 列使用我的 SUMPRODUCT 公式计算。所有 H 列值均正确。G 列值有时巧合匹配，但公式未产生预期结果。
我将使用 SUMPRODUCT 公式来解释计算过程。最后一行的公式已在图片上标注，因此我将参考这些公式：
=SUMPRODUCT(($B$3:$B$1001<>"")*($B$3:$B$1001<$C$3:$C$1001)*($A$3:$A$1001=E33))
正在过滤 B 列值以查找非空白。正在选择 B 列日期早于 C 列日期的记录。并且它仅选择 A 列值与 E 列中的最后一个值匹配的记录。
在这种情况下，A 列中的最后四条数据记录匹配 31 个活跃天数，其中没有一个在 B 列中为空白，因此不会被排除，并且它们在 B 列中的日期均不早于在 C 列中的日期。所以结果为零。
G33 中 COUNTIFS 版本的公式是：
=COUNTIFS($B$3:$B$33,"<"&$C$3:$C$33,$A$3:$A$33,E33)
这并没有明确过滤掉 B 列中的空白，但其余部分是将 SUMPRODUCT 逻辑转换为 COUNTIFS 结构。对于有 31 天活动的记录，B 列中没有空白，因此差异并不重要。然而，这是不匹配的结果之一。
如果有人想查看电子表格，我已将其保存到此处Google 表格链接错误。第一个选项卡是实际文件的大型摘录。有几个名为的选项卡Not Working，它们是我一直用来尝试诊断发生了什么情况的数据的小子集。上面的示例是从其中一个选项Not Working卡中提取和缩减的，仅包含一个派生列；它位于名为的选项卡上Sheet1。
我的问题是为什么 COUNTIFS 版本的公式没有产生相同的结果？
答案1
为什么 COUNTIFS 不能产生与 SUMPRODUCT 相同的正确结果？
COUNTIFS 是 COUNTIF 的扩展，因为您可以组合多个条件，但它受制于适用于 COUNTIF 的相同规则。电子表格中的 COUNTIFS 公式的基本问题是 COUNTIF 可以将一个范围与一个条件进行比较，但不能将一个范围与另一个范围进行比较。这就是为什么您在使用 COUNTIFS 版本时会得到奇怪的结果。
要将一个范围与另一个范围进行比较，您需要 SUMPRODUCT。您可以像之前一样完全使用 SUMPRODUCT 来完成此操作，也可以将 SUMPRODUCT 与 COUNTIF 结合使用以进行一些有限的比较，例如查找唯一值和重复项。有关此内容的优秀教程请访问Ablebits.com，包括如何处理许多常见问题。关于如何使用 COUNTIF 和 SUMPRODUCT 的介绍从页面的一半开始。
需要注意的另一个特点是 COUNTIF 和 SUMPRODUCT 处理范围的方式不同。SUMPRODUCT 是逐行计算的。COUNTIF 更注重聚合。因此，对于在列之间查找匹配值这样的任务，只有当同一行上的值匹配时，SUMPRODUCT 才会看到匹配，而 COUNTIF 会在范围内的任何位置看到匹配。
还有一些其他方法来比较范围，例如在数组公式中使用 SUM 和 IF 的组合。但使用不同的方法来复制使用 SUMPRODUCT 得到的正确结果是另一回事。