エクセル２００３ SUMPRODUCT

質問

いつも回答して頂き、とても感謝しています。

=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3)*(アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

上記の（アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4）のSheet2!C$4が空欄だった場合、左記を無効にして個数をカウントしたいのですが、どうすればよろしいでしょうか？

回答No.7

kagakusuki

>で、これを別の部分に入れ込んだ所、思った通りの結果（表示が０のまま）になりませんでした。下記が入れ込んだ式です。

との事ですが、

=SUMPRODUCT((INDIRECT("アラーム履歴一覧!"&"B3:B"&COUNTA(アラーム履歴一覧!$B:$B)+1)=$B5)*(LEFT(INDIRECT("アラーム履歴一覧!"&"E3:E"&COUNTA(アラーム履歴一覧!$E:$E)+1),LEN($I$1))=$I$1))

という関数の中には、回答No.1様の御回答の肝である

アラーム履歴一覧!$D$2:$D$100=IF(Sheet2!C$4="",アラーム履歴一覧!$D$2:$D$100,Shee2!C$4)

という部分が全く反映されておりませんから、「これを別の部分に入れ込んだ」事にはなっておりません。
　もしかしますと、最初の御質問の関数である

=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3)*(アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

という関数の中における

アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1

という条件を、Sheet2!$D$1の代わりに$I$1（Sheet2のI1セルではなく、その関数が入力されているシートのI1セル）を使用して、アラーム履歴一覧シートのE列に入力されている文字列の中で、$I$2セルと同じ文字列から始まっているもの、という条件に変更したいという事なのでしょうか？
　又、それと同時に、Sheet2!$B5やSheet2!C$3、Sheet2!C$4という箇所に関しても、参照先のセルを、Sheet2のセルではなく、その関数が入力されているシートのセルに変更したいという事なのでしょうか？
　それでしたら、次の様な関数となります。

=SUMPRODUCT((アラーム履歴一覧!$B$2:INDEX(アラーム履歴一覧!$B:$B,MAX(IF(COUNTIF(アラーム履歴一覧!$B:$B,"*?"),MATCH(CHAR(1),アラーム履歴一覧!$B:$B,-1),ROW(アラーム履歴一覧!$B$2)),IF(COUNT(アラーム履歴一覧!$B:$B),MATCH(9E+307,アラーム履歴一覧!$B:$B),ROW(アラーム履歴一覧!$B$2))))=$B5)*(アラーム履歴一覧!$C$2:INDEX(アラーム履歴一覧!$C:$C,MAX(IF(COUNTIF(アラーム履歴一覧!$B:$B,"*?"),MATCH(CHAR(1),アラーム履歴一覧!$B:$B,-1),ROW(アラーム履歴一覧!$B$2)),IF(COUNT(アラーム履歴一覧!$B:$B),MATCH(9E+307,アラーム履歴一覧!$B:$B),ROW(アラーム履歴一覧!$B$2))))=C$3)*((アラーム履歴一覧!$D$2:INDEX(アラーム履歴一覧!$D:$D,MAX(IF(COUNTIF(アラーム履歴一覧!$B:$B,"*?"),MATCH(CHAR(1),アラーム履歴一覧!$B:$B,-1),ROW(アラーム履歴一覧!$B$2)),IF(COUNT(アラーム履歴一覧!$B:$B),MATCH(9E+307,アラーム履歴一覧!$B:$B),ROW(アラーム履歴一覧!$B$2))))=C$4)+(C$4="")>0)*(LEFT(アラーム履歴一覧!$E$2:INDEX(アラーム履歴一覧!$E:$E,MAX(IF(COUNTIF(アラーム履歴一覧!$B:$B,"*?"),MATCH(CHAR(1),アラーム履歴一覧!$B:$B,-1),ROW(アラーム履歴一覧!$B$2)),IF(COUNT(アラーム履歴一覧!$B:$B),MATCH(9E+307,アラーム履歴一覧!$B:$B),ROW(アラーム履歴一覧!$B$2)))),LEN($I$1))=$I$1))

　但し、これでは関数の長さがExcel2003に入力可能な長さ超えておりますから、このままではExcel2003で使用する事は出来ないと思います。（Excel2007以降なら入力可能）

　そこで、適当なセルを作業用のセルとして使用する事にして、そのセル（ここでは仮にアラーム履歴一覧のG1セルとします）に次の様な関数を、まず入力して下さい。

=MAX(IF(COUNTIF(アラーム履歴一覧!$B:$B,"*?"),MATCH(CHAR(1),アラーム履歴一覧!$B:$B,-1),ROW(アラーム履歴一覧!$B$2)),IF(COUNT(アラーム履歴一覧!$B:$B),MATCH(9E+307,アラーム履歴一覧!$B:$B),ROW(アラーム履歴一覧!$B$2)))

　その上で、カウントする関数を次の様なものとして下さい。

=SUMPRODUCT((INDIRECT("アラーム履歴一覧!B2:B"&アラーム履歴一覧!$G$1)=$B5)*(INDIRECT("アラーム履歴一覧!C2:C"&アラーム履歴一覧!$G$1)=C$3)*((INDIRECT("アラーム履歴一覧!D2:D"&アラーム履歴一覧!$G$1)=C$4)+(C$4="")>0)*(LEFT(INDIRECT("アラーム履歴一覧!E2:E"&アラーム履歴一覧!$G$1),LEN($I$1))=$I$1))

回答No.6

keithin

＞で入れ込んだ箇所ですが、
＞(LEFT(INDIRECT("アラーム履歴一覧!"&"E3:E"&COUNTA(アラーム履歴一覧!$E:$E)+1),LEN($I$1))=$I$1)の箇所です。
＞=以降に入れたのですが、この場合、無理なんでしょうか？

「=以降に入れた」とは、「=$I$1」のところを何か細工したという事ですか。
今回ご相談の内容と全く無関係の数式なので、先の回答とどう関連付けたのか、具体的にどこをどう細工したのか皆目見当がつきません。

もう何度もお願いしていると思いますが、新しいご相談は「ついでに聞いちゃえ」じゃなく、「今回はこういう状況で具体的にこういう数式を組み立てました」と、キチンと情報を整理して新しく投稿してください。

いずれにしても「=$I$1」の箇所を細工したなら、数式中には他にも$I$1がありますので、そちらも漏れなく細工しないといけないんじゃないかなと思います。

回答No.5

kagakusuki

回答No.2です。

>さっそく貼り付けてみた所、他の条件が無視されて、D列に入力されている個数が表示されました。

との御返事を頂きましたので、確認してみましたが、「アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5」や「アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3」、「アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1」といった他の条件が無視されて、D列に入力されている個数のみがカウントされるという事は御座いませんでした。
　念のため、

=IF(Sheet2!C$4="",
SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1)),
SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3)*(アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1)))

という関数を入力して、回答No.2の関数と比較してみましたが、カウント結果に差が生じる事は御座いませんでした。
　貼り付けを行った際に、どこかをお間違えになられているのではないでしょうか？

>自分が思ったような事はNo.1さんの式で実現されています。

との事ですので、念のために、その関数も入力して比較しようと試みましたが、当方が使用しているExcel2010では何故か、Sheet2!C$4が空欄ではない場合には#REF!エラーが出てしまうため、残念ながら回答No.1様の関数と比較を行う事は出来ませんでした。

回答No.4

kagakusuki

回答No.2です。

　次の様な関数でも同じ事が出来ます。

=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100&""=""&Sheet2!C$3)*((アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)+(Sheet2!C$4="")>0)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3)*((アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)+(Sheet2!C$4&""="")>0)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100&""=""&Sheet2!C$3)*((アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)+(Sheet2!C$4&""="")>0)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100&""=""&Sheet2!C$3)*((アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)+(Sheet2!C$4="")&"">""&0)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3)*((アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)+(Sheet2!C$4&""="")&"">""&0)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100&""=""&Sheet2!C$3)*((アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)+(Sheet2!C$4&""="")&"">""&0)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

　尚、この回答における上記の関数や、回答No.2の関数の様に、

((「アラーム履歴一覧!$D$2:$D$100の範囲内のセルがSheet2!C$4と等しい」)+(「Sheet2!C$4が空欄となっている」)>0)

という条件をカウントする条件の1つとして使用する場合、その末尾の所にある「>0」（或いは「&"">""&0」）という条件を忘れずに付ける様にして下さい。
　もし、「>0」を付け忘れていますと、もしも、アラーム履歴一覧!$D$2:$D$100の範囲内に空欄のセルがあった場合において、Sheet2!C$4を空欄のままカウントしますと、アラーム履歴一覧!のD列が空欄となっている行の所を、重複してカウントしてしまう事になりますので、正しいカウント数よりも多くカウントしてしまう恐れがあります。
　例えば、Sheet2!C$4が空欄となっている場合において、

(アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1)

という条件を満たしている行が、アラーム履歴一覧!のD列が空欄となっている行の所に3箇所、アラーム履歴一覧!のD列に何らかの値が入力されている行の所に4箇所あったとしますと、正しいカウント数は

3＋4＝7箇所

ですが、「>0」を付け忘れた場合には、

3×(「アラーム履歴一覧!のB列の値がSheet2!C$4の値に等しい」＋「Sheet2!C$4が空欄に等しい」)＋4＝3×(1＋1)＋4＝10箇所

という誤ったカウント数を求めてしまう事になります。

回答No.3

jcctaira

kero1192keroさん
こんにちは。

=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3)*((アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)+(Sheet2!C$4&""=""))*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

上記の式でできると思います。
ご確認下さい。

お礼

返事が遅くなってすみません。
さっそく貼り付けてみた所、他の条件が無視されて、D列に入力されている個数が表示されました。自分が思ったような事はNo.1さんの式で実現されています。

回答No.2

kagakusuki

その場合は次の様にします。

=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3)*((アラーム履歴一覧!$D$2:$D$100=Sheet2!C$4)+(Sheet2!C$4="")>0)*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

お礼

返事が遅くなってすみません。
さっそく貼り付けてみた所、他の条件が無視されて、D列に入力されている個数が表示されました。自分が思ったような事はNo.1さんの式で実現されています。

回答No.1

keithin

たとえば：
=SUMPRODUCT((アラーム履歴一覧!$B$2:$B$100=Sheet2!$B5)*(アラーム履歴一覧!$C$2:$C$100=Sheet2!C$3)*(アラーム履歴一覧!$D$2:$D$100=IF(Sheet2!C$4="",アラーム履歴一覧!$D$2:$D$100,Shee2!C$4))*(アラーム履歴一覧!$E$2:$E$100=Sheet2!$D$1))

お礼

いつも回答して頂き、とても感謝しています。
▼
keithinさんの提示例を参考にして、会社で作りこんだ所、自分が思っていた通りの結果になりました。ありがとうございました。 で、これを別の部分に入れ込んだ所、思った通りの結果（表示が０のまま）になりませんでした。下記が入れ込んだ式です。

=SUMPRODUCT((INDIRECT("アラーム履歴一覧!"&"B3:B"&COUNTA(アラーム履歴一覧!$B:$B)+1)=$B5)*(LEFT(INDIRECT("アラーム履歴一覧!"&"E3:E"&COUNTA(アラーム履歴一覧!$E:$E)+1),LEN($I$1))=$I$1))

で入れ込んだ箇所ですが、
(LEFT(INDIRECT("アラーム履歴一覧!"&"E3:E"&COUNTA(アラーム履歴一覧!$E:$E)+1),LEN($I$1))=$I$1)の箇所です。=以降に入れたのですが、この場合、無理なんでしょうか？