エクセル関数のSUMPRODUCTについて教えてください。

質問

複数条件を満たす合計を出したくて「SUMPRODUCT関数」を考え付いたのですが、「～で始まる文字」など曖昧な条件の時にはどのようにしたらよいのでしょうか？
ワイルドカード文字（*）を使ってみたのですが、うまくいかず困っています。よろしくお願いいたします。

コード　摘要　　　　収入
１　　　東組南東　　１００
１　　　北東組北西　１５０
１　　　東組北西　　１００　
１　　　北東組南西　１５０

コード「１」で摘要が「東組」で始まる収入の合計を出したい場合には、どのような関数が考えられますでしょうか？

回答No.3

ham_kamo · ベストアンサー

No.2です。
別シート（Sheet1）にデータが入っているときの式は、tikorockさんのお書きになった

=SUMPRODUCT((Sheet1!A2:A5=1)*(LEFT(Sheet1!B2:B5,2)="東組")*(Sheet1!C2:C5))

であってるはずです。実際に試してみましたが、エラーにならず、ちゃんと数値が出ました。
逆にどうしたら#N/Aのエラーが出るか試してみると、

=SUMPRODUCT((Sheet1!A2:A6=1)*(LEFT(Sheet1!B2:B5,2)="東組")*(Sheet1!C2:C5))

のように、参照しているA,B,C列の範囲が違う（上の例だとA列だけ6行目までになっている）場合に、そのようになることがわかりました。
実際の表はもっとデータ量が多いかと思いますが、数式で指定した範囲がずれたりしていないか確かめてみてください。

お礼

早速のご回答ありがとうございます！
ham_kamoさんのご推測通り、Ａ，Ｂ，Ｃ列の範囲が違っていました。このような場合には、指定範囲がずれるとエラーが出るのですね。大変勉強になりました。ありがとうございました！！

回答No.2

ham_kamo

１行目がタイトル行で、Ａ列、Ｂ列、Ｃ列にそれぞれコード、適用、収入のデータが入っているとして、

=SUMPRODUCT((A2:A5=1)*(LEFT(B2:B5,2)="東組")*(C2:C5))

とすればいかがでしょうか。
ただし、「北東組」から始まるものを探すときは、

=SUMPRODUCT((A2:A5=1)*(LEFT(B2:B5,3)="北東組")*(C2:C5))

のようにLEFT関数の引数をその文字数に変える必要があります。

条件の文字列をセルから取ってくる場合、たとえばD1に「東組」と書いてあって、それで集計したい場合は、

=SUMPRODUCT((A2:A5=1)*(LEFT(B2:B5,LEN(D1))=D1)*(C2:C5))

のようにすればよいでしょう。

補足

ご丁寧な回答をありがとうございます。「LEFT」を使えばよかったのですね！
重ねて質問させていただきたいと思います。
コード、摘要、収入のデータがsheet1にあるとして、sheet2に数式を入れて合計を表示したい時は同じ要領でよいのでしょうか？

=SUMPRODUCT((Sheet1!A2:A5=1)*(LEFT(Sheet1!B2:B5,2)="東組")*(Sheet1!C2:C5))

と入力してみたのですが、#N/Aのエラーが出てしまいました。
何度も申し訳ありませんが、ご回答いただけたらありがたいです。

回答No.1

merlionXX

=SUMPRODUCT((A2:A5=1)*(LEFT(B2:B5,2)="東組")*(C2:C5))

かな。

お礼

早速のご回答ありがとうございます！