複数のシートにまたがるCOUNTIFはSUMPRODUCTでは機能しません

Question

以下の2枚のシートがあります。

（上記の表の例は、この質問のために大幅に簡略化されています）

2017 年の A の総出現回数を加算する数式を記述しようとしています (答えは 3)。

この数式は、各シートに対して 2 つの 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")

これらを以下の 1 つの数式に結合しようとしました。ただし、#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