跨多张工作表的 COUNTIF 不适用于 SUMPRODUCT

Question

我有下面两张纸。

（上面的示例表针对这个问题进行了高度简化）

我正在尝试编写一个公式，将 2017 年内 A 的总出现次数相加（答案是 3）。

如果我为每张工作表添加两个 COUNTIF，则此公式有效：

=COUNTIF(INDEX(Sheet2!A2:C4, 0, MATCH(2017, Sheet2!A2:C2, 0)), "A") + COUNTIF(INDEX(Sheet3!A2:C4, 0, MATCH(2017, Sheet3!A2:C2, 0)), "A")

我尝试将它们合并到下面的单个公式中。但是我收到 #Value 错误。“Sheets”是一个由 {Sheet2, Sheet3} 组成的命名范围。

=SUMPRODUCT(COUNTIF(INDEX(INDIRECT("'"&Sheets&"'!"&"A2:C4"), 0, MATCH(2017, INDIRECT("'"&Sheets&"'!"&"A2:C2"), 0)), "A"))

当我尝试使用命名范围时，为什么上述公式会给出错误？

Answer 1

我已经成功地在表格之间复制了第一个公式，并且正在工作，但需要SUMPRODUCT稍加修改，应该这样写，这样你就能得到正确的结果3：

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!A3:C4"),"A")-COUNTIF(INDIRECT("'"&Sheets&"'!A2:C2"),"2017"))

注意：

• 命名范围Sheets由工作表名称和所使用的公式组成。

• A在上面的公式中，它首先找到值为的单元格数量5，然后减去值为 的单元格数量2017，2然后公式返回3,,, (5 As - 2 2017s) = 3。

• 如果您使用+通常与多个一起使用的符号COUNTIF，那么在您的情况下您将获得 7，并且如果 COUNTIF 被,公式返回分隔5

:编辑:

由于有时上面显示的公式不能正常工作因此我想建议这个数组（CSE）公式：

{=SUM(IF(ISNUMBER(Sheet1!$A$170:$C$170),IF(Sheet1!$A$170:$C$170=2017,IF(Sheet1!$A$171:$C$172="A",1))))+SUM(IF(ISNUMBER(Sheet2!$A$170:$C$170),IF(Sheet2!$A$170:$C$170=2017,IF(Sheet2!$A$171:$C$172="A",1))))}

• 完成它Ctrl+Shift+Enter