エクセルで複数条件に合致する合計を求める方法

お手数ですが、教えてください。
例えば
下記シート１として、A列に日付、そしてB列に数字を入力します。

シート１
　　　A　　　 　B
1　 1月1日　　　５
2 　 1月1日　　－７
3 　 1月1日　　－１０
4 1月2日　　　８
5 1月3日　　　９
6 　 1月3日　　－９
7　　　1月3日　　－３
8　　 1月4日　　　１
9　　 1月4日　　　１０
10　 　1月4日　　－６
11　 　1月4日　　－１１

これをシート２に下記のような日付ごとのプラス数字の合計と
マイナス数字の合計を表示させたいのですが、、
A列に日付、B列にシート１B列のプラスの数字の合計
C列にシート1のB列のマイナスの数字の合計を表示したいのです。

シート２
　　　A　　　 　B C
1　 1月1日　　　５　　　－17
2 　1月2日　　　８　　　（０か無表記）
3 　1月3日　　　９　　　－12
4 　1月4日　　１１　　　－17

SUMPRODUCTという関数を使うのかと思って色々トライしてるのですが
うまくいきません。
説明不足でややこしいのかもしれませんが、お解りになる方がいらっしゃれば
是非ご教授をお願いしたいと思います。

No.1 ベストアンサー 回答者： siffon9 回答日時： 2017/01/09 00:01

こんな感じですか。

B1
=SUMPRODUCT((Sheet1!$B$1:$B$11)*(Sheet1!$A$1:$A$11=$A1)*(Sheet1!$B$1:$B$11>=0))

C1
=SUMPRODUCT((Sheet1!$B$1:$B$11)*(Sheet1!$A$1:$A$11=$A1)*(Sheet1!$B$1:$B$11<0))

この回答へのお礼

感激です！
早速のご教授ありがとうございます！
うまくいきました！
惜しいところまで出来てたのですがまだまだ勉強不足です。
お忙しいところ本当にありがとうございます！

お礼日時：2017/01/09 00:47

No.2 回答者： 銀鱗 回答日時： 2017/01/09 00:25

SUMIFS関数でできるような気がしますが…これはExcel2007以降で使える関数です。

　＝SUMIFS(合計対象範囲,検索条件範囲1,検索条件1,検索条件範囲2,検索条件2...)
のようにして使います。

Sheet2 B1セル
　＝SUMIFS(Sheet1!B:B , Sheet1!A:A,A1 , Sheet1!B:B,">0")
Sheet2 C1セル
　＝SUMIFS(Sheet1!B:B , Sheet1!A:A,A1 , Sheet1!B:B,"<0")
あとは必要な行までセルをコピーする。

とってもシンプル。


・・・本題・・・
SUMPRODUCT関数を使う場合は、この関数が
　横方向に掛け算、
　その結果を縦方向に足し算、
であることをしっかり念頭に入れておきましょう。

まずは横一列だけで考えます。
すると、
　＝SUPRODUCT（ （シート1のA列の日付＝シート２のA列の日付）×1 　,　（シート1のB列＞0）×1 　,　シート1のB列）
　＝SUPRODUCT（ （1月1日＝1月1日）×1 　,　（5＞0）×1　 ,　Sheet1!B:B　）
のようにすることができると分かると思います。
この時、比較の結果は「True」「False」として返ってきますが、これに「1」を掛けると「True」は「1」、「False」は「0」になります。
また、「True」「False」と「数値」を掛け算すると前述のような判断が行われた「数値」として返ってきます。
すると比較を含んだ項目同士を掛け算してくれます。
　＝SUPRODUCT（ （1月1日＝1月1日）×1 　,　（5＞0）×1　 ,　Sheet1!B:B　）
　＝SUPRODUCT（ （Ture）×1 　,　（True）×1　 ,　5　）
　＝SUPRODUCT（ １ 　,　1　 ,　5　）
　＝１×１×５
　＝５
これを忘れていると正しい結果が返ってきません。

そして別のシートを参照するときは、
　＝シート名＋！＋セル番地
のようにして参照です。

あとはシート１に対しては配列（A列全体とかB列全体）を指定すればいい。
　＝SUPRODUCT（ （Sheet1!A:A＝A1)*1 　,　（Sheet2!B:B＞0)*1 　,　Sheet1!:B:B）
または
　＝SUPRODUCT（ （Sheet1!A:A＝A1) 　*　（Sheet2!B:B＞0) 　*　Sheet1!:B:B）

そして注意が必要なのが、日付。
これ、月日の表示なので年が一致するかどうかの判断が見た目では分からない。
できれば年まで表示させるようにしておこう。

・・・余談・・・
合計なので、シート1のB列の0は考慮しなくて良いと思います。
ですので自分の示した数式でシート1のB列の符号を判断するために「>=」「<=」は使わず「＞」「＜」のみを使用しています。

この回答へのお礼

ご丁寧なご教授誠に感謝します！
時間を割いていただき詳しいご説明に感激です。
大変勉強になりました。
ありがとうございます。

お礼日時：2017/01/09 12:44