Excelでの条件付合計関数について

質問

お世話になります。
Excelで以下のような複数の条件での合計値の算出を行いたいのですが、
上手く算出できません。
環境：WindowsXP,Excel2000

名前　　 色　　　 値段
りんご　 赤　　　 100
りんご　 青　　　 150
りんご　 赤　　　 100
ぶどう　 赤　　　 200
ぶどう　 青　　　 200

上記のようなリストがあるとき、
「名前がりんごで色が赤なら金額を合計する」
「名前がぶどうで色が青なら金額を合計する」
などのように2つの条件に一致する場合、別のセルの合計金額を算出したいのですが、SumIF関数だと条件を一つしか設定できない、
また、DSUM関数だと違う条件に都度タイトルを設けなくてはならないため、
どのように算出して良いかわからず困っています。
Sum(If(AND(A1:A5="りんご",B1:B5="赤"),C1:C5)
などのようにしても上手く計算されませんでした。

どなたかアドバイス頂ければ幸いです。
よろしくお願い致します。

回答No.7

redowl · ベストアンサー

#3です
＞こちら試してみたのですが答えが"0"になってしまいました。
例えば「りんご」の入っているセル「りんご＿」
スペースがくっついて４文字に（４文字以上）に、なっていたりしてませんか？

お礼

！！！出来ました！！！ありがとうございます！
ご指摘の通り、こちらに掲載したリストをテキストデータでExcelに貼り付けていたので、
空白が含まれていました。
空白を削除したら上手く動作しました！

どうもありがとうございました。
助かりました。m(__)m

回答No.6

mshr1962

>Sum(If(AND(A1:A5="りんご",B1:B5="赤"),C1:C5)
=Sum(If((A1:A5="りんご")*(B1:B5="赤"),C1:C5))
と入力してCtrl+Shift+Enterで配列数式にする。
最後のCtrl+Shift+Enterが絶対必要です。
別解として他の方の掲示されたSUMPRODUCT関数を利用する方法と
「データ」「ピボットテーブル」を使用する方法があります。
※並び替えが可能なら、「データ」「集計」を２重（商品名,色）で行えば可能です。

お礼

すみません。
こちらもredowlさんのご指摘通り空白を見直したらちゃんと動作しました！

ありがとうございました。
とても助かりました！！

補足

ありがとうございます。
配列数式にしてみました。
式の前後に{}がつきました。
#VALUEだった答えが"0"になりましたが合計値は得られませんでした。。何故？？

合計の集計票を出来れば別シートに表示したいので、
集計機能などを使わずに関数で処理できればと思いました。
我侭言ってすみませんがよろしくお願い致します。

回答No.5

merlionXX

名前がりんごで色が赤なら金額を合計する」
=SUMPRODUCT((A2:A6="りんご")*(B2:B6="赤")*C2:C6)

「名前がぶどうで色が青なら金額を合計する」
=SUMPRODUCT((A2:A6="ぶどう")*(B2:B6="青")*C2:C6)

補足

ありがとうございます。
こちらも試したらやはり答えが"0"になりました。。
何がいけないのでしょうか。。

回答No.4

ipsum11

ちょっと式の編集が面倒になりますが、
SUM(IF(OR(AND(A2="りんご",B2="赤"),AND(A2="ぶどう",B2="青")),C2,0))
というのはどうでしょう？あとはリストが増える毎にSUM関数の引数をIF(OR(AND(A3="りんご",B3="赤"),AND(A3="ぶどう",B3="青")),C3,0)と言う風に増やしていけば合計が算出できます。

補足

アドバイスありがとうございます。
こちらって全ての列に対してAND()で条件を追記しなければならないのですよね？
だとすると、数百行あるのでちょっと難しい気がしました。
行数の少ないもので利用させて頂ければと思います。
ありがとうございました。

回答No.3

redowl

=SUMPRODUCT((A1:A5="りんご")*(B1:B5="赤")*(C1:C5))

では如何？

補足

アドバイスありがとうございます。
こちら試してみたのですが答えが"0"になってしまいました。
=SUMPRODUCT((A2:A6="りんご")*(B2:B6="赤")*(C2:C6))
で試しても同じでした。
Excelのバージョンの問題でしょうか。。

回答No.2

lele5050

シンプルに、いくつかの関数に分けてしまうのはダメでしょうか？

例えば、値段の右側の列に、「countif名前のセルがりんご」、
その右の列に「countif色のセルが赤」として
その右に、これら２つの掛け算を入れます。

そうすると、両者に合致していれば1、どちらかに合致していなければ0になります。

ぶどうも同様にします。

そして、りんごとぶどうの答えセルを足し算したセルを作ります。

そのセルと値段をかければ、条件に合わないものは０になります。

あとは、それらをsumで合計すれば、条件に合う合算が出来ると思います。

回りくどい感じですが。。。
工夫すれば、シンプルに出来るかもしれません。

ただ、１つ１つばらした数式にしておいた方が、後でロジックを追いかける場合にやりやすい気もします。

補足

アドバイスありがとうございます。
grumpy_the_dwarfさんへの補足分にも書かせていただいたのですが、
何とか関数を一つでできないかなと思い試みていたところです。

回答No.1

grumpy_the_dwarf

AND条件ならA1&B1な列Dを作って、

=sumif(D1:D5, ="りんご赤", C1:C5)

とするだけ。

補足

アドバイスありがとうございます。
列を追加すれば勿論簡単なのですが、
追加せずにできないかなと思ってお伺いしてみた次第です。
もし上手く行かないようであればアドバイスいただいたように列を追加しようと思います。