跨多個工作表的 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在上面的公式中，它首先找到值為are的單元格的數量5，然後減去值為2017, are 的單元格的數量2，然後公式返回3,,, (5 As - 2 2017s) = 3。

• 如果您使用+通常與 multiple 一起使用的符號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