SUMPRODUCT関数で複数条件適用されない

質問

Sheet1
A列：日付
B列：注文番号
C列：品名
D列：整理番号
E列：数量

集計
A列：品名
B～AF列：日付（1～31日）

【集計】B～AF列の日付、A列の品名が
【Sheet1】A列の日付、C列の品名と一致したら
【Sheet1】E列の数量を【集計】の該当するセルに抽出する
式をSUMPRODUCT関数で作成しました。

例）10/1　りんご　20

【集計】B3セルに以下の数式を入れましたが
なぜか数量が「0」のままになります。
=SUMPRODUCT('Sheet1'!$A$3:$A$200='集計'!B$2)*('Sheet1'!$C$3:$C$200="りん
ご")*('Sheet1'!$E$3:$E$200)

特に数式のミスはないと思うのですが
どうしても正しい値が入りません。

数式に間違いがあればご指摘いただけませんでしょうか。
また、他に良き方法があればご教示いただけるとありがたいです。

回答No.6

tom04 · ベストアンサー

No.4・5です！
何度もごめんなさい。

もう一つ見逃していたコトがありました。
お示しの数式をよくよく確認してみると
Sheet名の前後にアポストロフィーらしきものがついていますね？
これは必要ありません。
それと括弧の数も少ないと思います。
（数式全体を見ていて、細かい部分を見逃していました）

No4で当方が投稿した数式をこの画面からNo.4の数式を
そのままコピー＆ペーストしてみてください。

おそらく大丈夫だと思います。m(_ _)m

お礼

アポストロフィーが問題ではなく、まさに括弧の数が問題でした！
別のファイルで同じような数式でOKだった記憶なのです(笑)
やはり確認は作成した人間が何度やってもだめですね。。
別の人間に確認してもらったら間違いが発見できていたやも知れないですね。。

ほんとにありがとうございました！
的確なご指摘、感謝してもしきれないです！

補足

ほんと、ご丁寧に確認までしていただいて恐縮です。

ひとつ訂正です。
わかりやすいようにSheet1としてありますが実はシート名が【出荷台帳】となっております。

そこでご指摘のアポストロフィーが前後についているとの件ですが、シートを''がついてしまいます。
Sheet1ｔいう名称の場合は、つかないようですね。。。。

せっかくご丁寧にご教示くださったのに、ほんとに申し訳ございません。

回答No.5

tom04

No.4です！
たびたびごめんなさい。

投稿後一つだけ気になることがありました。

お示しの数式には「集計!」が含まれていますが、
「集計」Sheetに数式を入れる場合、Sheet名を指定する必要はないと思います。

もちろんSheet名を入れても構いませんが、数式を入れたSheet名が
ちゃんと「集計」となっているかどうかというコトも確認してみてください。

どうも失礼しました。m(_ _)m

お礼

ご指摘ありがとうございます！

回答No.4

tom04

こんばんは！
↓の画像はExcel2003でやってみたものです。

「集計」SheetのB3セルに
=IF($A3="","",SUMPRODUCT((Sheet1!$A$3:$A$200=B$2)*(Sheet1!$C$3:$C$200=$A3)*(Sheet1!$E$3:$E$200)))
という数式を入れ列方向・行方向にオートフィルでコピーしています。
ちゃんと表示されました。

一つ気になるのが日付がシリアル値かどうか？というコトですが、
No.2さんの補足を読ませていただくと別段問題ないように思われます。

ちなみに↓の画像の「集計」SheetのB2セルには
=IF(MONTH(DATE($A$1,$C$1,COLUMN(A1)))=$C$1,DATE($A$1,$C$1,COLUMN(A1)),"")
という数式を入れAF列までオートフィルでコピーしています。
（セルの表示形式はユーザー定義から　d　）

品名に関しては問題ないというコトなので、日付部分をもう一度確認してみてはどうでしょうか？

それでもダメならごめんなさいね。m(_ _)m

お礼

回答ありがとうございます！
ご丁寧に入力までしていただいて、お手数おかけしてます；；
ご提示の数式で一度やってみます！

回答No.3

keithin

再掲：
＞あなたが「入れたつもり」の内容でご相談も書かれていますが，それぞれのセルを選んだ時に数式バーに実際に現れる内容を良く確認して，一致している，いないを確認しなければなりません。

自分のやったことに間違いは無いと思い込んでるのでしたら，残念ですがフォローできる内容はありません。

間違いはないはずだと思いながらも，データを入れ直してみる，新しいシートで試しにやり直してみるなどしてみると，良い結果が得られるかもしれません。

補足

散々確認したうえで質問を投稿させていただいております。
もちろん、式の入力をやり直したり別のシートで確認したりしました。
それでも結果が得られないので質問しているのです。

こうなると、もうお手上げでしょうか？

他者様から見て、質問させていただいている数式に誤りは見当たらないのですよね？
諦めざるを得ないのでしょうか。

回答No.2

keithin

ご利用のエクセルのバージョンが不明ですが，Excel2007以降を使いSUMIFS関数で計算するのが良い方法です。

現在の数式でゼロになってしまう理由は，
１）シート１のＡ列の「日付」の具体的な内容と，集計シートの２行目の「日付」の具体的な内容が食い違っている
　たとえばシート１は去年の日付，集計シートでは今年の日付になっている
　たとえばシート１はちゃんと年/月/日で記入しているのに，集計シートでは「１」などのように違う内容が書かれている
　など。
　あなたが「入れたつもり」の内容でご相談も書かれていますが，それぞれのセルを選んだ時に数式バーに実際に現れる内容を良く確認して，一致している，いないを確認しなければなりません。

２）シート１のＣ列の商品名称が，たとえば「りんご」じゃなく「りんご□」（□はスペース）などのように食い違っている

などが原因と推測できます。

補足

大変失礼致しました。
Excel2003です。
日付内容同じで、表示形式だけが異なります。
2012/10/1→2012/10/1
2012/10/1→10/1
このように、右のように表示される設定としています。

またご指摘の品名入力についてですが、プルダウンリストから選択となりますので無駄なスペース等が入力されていることはありません。

回答No.1

mu2011

Sheet1のＡ列日付と集計２行目日付の書式が相違しているからだと思いますが如何でしょうか。

補足

表示形式は違いますが、そのせいではないようです。