SUMPRODUCT 複数条件設定で、計算式結果がおかしいです。

質問

下記のような表があったと仮定します。

　　A　　B　　　C　　　D
1　月度　店舗　品目　売上金額
2　4　　　A　　　あ　　22300
3　4　　　B　　　あ　　18700
4　4　　　C　　　あ　　14500
5　4　　　A　　　い　　17950
6　5　　　B　　　あ　　44000
7　5　　　C　　　い　　35000
8　5　　　A　　　う　　12000
9　5　　　B　　　い　　13400

この表から、
店舗「A」、品目「あ」の売上合計は、22300になり、
数式「=SUMPRODUCT(($B$2:$B$9="A")*($C$2:$C$9="あ")*$D$2:$D$9)」で計算できます。

店舗「A,B」、品目「あ」の売上合計は、85000になり、
数式「=SUMPRODUCT(($B$2:$B$9={"A","B"})*($C$2:$C$9="あ")*$D$2:$D$9)」で計算できます。

店舗「A」、品目「あ,い」の売上合計は、40250になり、
数式「=SUMPRODUCT(($B$2:$B$9="A")*($C$2:$C$9={"あ","い"})*$D$2:$D$9)で計算できます。

しかし、
店舗「A,B」、品目「あ,い」の売上合計は、116350なのですが、
数式「=SUMPRODUCT(($B$2:$B$9={"A","B"})*($C$2:$C$9={"あ","い"})*$D$2:$D$9)」では、数値がおかしく（35700に）なります。

数式の使い方が間違っているのでしょうか？
アドバイスをお願いします。

回答No.1

papayuka · ベストアンサー

「A」が「あ」と、「B」が「い」の合計ですね。
こちらの数式の原理
http://miyahorinn.fc2web.com/faq/faq100.html

SUMPRODUCTとか配列関数とかを熟知してませんので、もっとましな方法があるかも知れませんけど、、、
=SUMPRODUCT(($B$2:$B$9={"A","A","B","B"})*($C$2:$C$9={"あ","い","あ","い"})*$D$2:$D$9)
とか。

お礼

紹介していただいた数式で、答えは求めることができました。
なぜそうなるのかは理解できていないため、これから勉強していきます。
今回はありがとうございました。

回答No.6

Wendy02

こんにちは。
#5の回答者です。

>いろいろな方法があるのですね。
>なぜそうなるのかは理解できていないため、これから勉強していきます。

ちょっと思うことですが、いくつかの方法は、掲示板ならではの回答だと思います。
掲示板の回答って、省スペースなものになる傾向が強いようです。

ただ、やっぱり

>補助列を使って、
>
>E2: ～下へ
>=AND(OR(B2="A",B2="B"),OR(C2="あ",C2="い"))
>
>=SUMIF($E$2:$E$9,TRUE,$D$2:$D$9)

の方法が、オーソドックスではないでしょうか？
自分の書いたものでも、後で読みきれない数式がありますから、納まりの良さというものは、実務でも、必ずしも必要ないと違いますか？

お礼

わざわざ補足ありがとうございます。
確かに理解していない数式を使うと後々、苦労するかも知れませんね。
補助列の場合は、後から見ても判りやすいので状況に応じて、
使い分けしようと思います。

回答No.5

Wendy02

こんにちは。

----------------------------------
1. データベース関数なら、

DataBase:
=$A$1:$D$9

Criteria:
=$H$1:$I$5

H　　　I
店舗　　品目
A　　　　あ
B　　　　い
A　　　　い
B　　　　あ

D1: (念のため）
売上金額

=DSUM(database,D1,Criteria)
----------------------------------------
配列数式なら、

=SUMPRODUCT(($B$2:$B$9&$C$2:$C$9={"Aあ","Aい","Bあ","Bい"})*$D$2:$D$9)

複雑になると、ミスが多くなりそうです。
----------------------------------------
補助列を使って、

E2: ～下へ
=AND(OR(B2="A",B2="B"),OR(C2="あ",C2="い"))

=SUMIF($E$2:$E$9,TRUE,$D$2:$D$9)

という式でもよいと思います。
-----------------------------------------

お礼

いろいろな方法があるのですね。
なぜそうなるのかは理解できていないため、これから勉強していきます。
今回はありがとうございました。

回答No.4

grumpy_the_dwarf

($B$2:$B$9={"A","B"})という式が返す結果は
{TRUE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;...}
という2列の配列になります。これを踏まえて、

($C$2:$C$9="あ")という式は
{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}
という1列の配列なのですが、それでは計算出来ないので列数の一番
大きな配列に揃えて
{TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;FALSE,FALSE;TRUE,TRUE;...}
と扱います。これが2番目と3番目の式で一見or条件での計算が成り
立っていた理由です。

ここで、($B$2:$B$9={"A","B"})*($C$2:$C$9={"あ","い"})とすると
{TRUE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;...}
と
{TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;...}
のANDになるので、結果はpapayukaさんやimogasiさんの喝破したと
おり、(A and あ) or (B and い)だけがTRUEになってしまいます。

というわけで、中括弧は配列の記述だと決まっているのにor条件の
つもりで使うのは危険であることの解説でした。解決法は、他の回
答者の皆さんがおっしゃるとおり、マジメにor条件を評価していく
ことですね。

お礼

なぜだめか、詳しく回答していただきありがとうございます。
この手の、複数列の配列数式についてのサイトがありましたらご紹介をお願いします。

回答No.3

imogasi

>店舗「A,B」、品目「あ,い」の売上合計は
この意味があいまいでしょう。質問者には判っているが、一般的に、こう書いても判らない。文章で正確に表現すべし。
ただ合計116350だと質問者が言うので、それに当たる組み合わせを、回答者が探さないといけない。
"→第２条件
↓第１条件" あ い う
A ○ ○ ✕
B ○ ○ ✕
C ✕ ✕ ✕
らしい。
ーー
配列数式でやると
=SUM(IF(((B2:B9="A")+(+B2:B9="B"))*((C2:C9="あ")+(C2:C9="い")),D2:D9))
と入れて
SHIFTとCTRKとENTERキーを押す。　結果　116350
ーーー
SUMPRODUCTの書き方に書き換えると
=SUMPRODUCT(((B2:B9="A")+(+B2:B9="B"))*((C2:C9="あ")+(C2:C9="い"))*(D2:D9))
結果　116350
両方とも「＋」は「または」の場合に使う。「＊」は「および」の意味で使うのは、ご存知でしょう。
｛"A","B"}は確かに両方の要素について行うことになると思うが、
なぜダメなのか、小生の知識が整理できていないので、自分流の回答をとりあえず出す。
選択条件A,Bなどの項目数が多いと、そういう表現ができればよいですが。

お礼

>この意味があいまいでしょう。質問者には判っているが、一般的に、こう書いても判らない。文章で正確に表現すべし。
申し訳ありません。今後の教訓にいたします。

>両方とも「＋」は「または」の場合に使う。「＊」は「および」の意味で使うのは、ご存知でしょう。
そうなんですか？知りませんでした。これから勉強して理解します。

今回はありがとうございました。

回答No.2

sige1701

No1さんが回答していますので理由については略

こんな方法でも
=SUMPRODUCT((($B$2:$B$9="A")+($B$2:$B$9="B"))*(($C$2:$C$9="あ")+($C$2:$C$9="い"))*$D$2:$D$9)

お礼

こちらも同様に紹介していただいた数式で、答えは求めることができました。
なぜそうなるのかは理解できていないため、これから勉強していきます。
今回はありがとうございました。