SUMPRODUCT関数で困っています

質問

エクセル2007にて、SUMPRODUCT関数を使ってデータ集計をしたいが
下記の現象で困っています。

SUMPRODUCTの特徴として参照先(sheet1)の指定範囲
「($B$2:$E$6)の範囲を($B$2:$G$20)」を変更すると
SUMPRODUCT関数がn/aになり値が表示できない。

(やりたいこと)
sheet1に元データが入力されています。
　　　A　　 　B　　 　C　　 　D　　 　E
1 NO 社名 商品 購入日 金額
2 01 A商事 肉 1/3 50円
3 02 B電気 野菜 1/10 70円
4 02 B電気 肉 1/12 50円
5 03 C工業 魚 1/20 60円
6 03 C工業 肉 1/30 50円

sheet2はsheet1より必要なデータを、NOをキーにして、 社名・商品・金額を
VLOOKUP関数にて取ってきて表示しています。
　　　A　　 　B　　 　C　　 　D　　 　E
1 NO 社名 商品 金額
2 01 A商事 肉 50円
3 02 B電気 野菜 70円
4 02 B電気 肉 50円
5 03 C工業 魚 60円
6 03 C工業 肉 50円
6 03 C工業 鉄 90円

sheet3はsheet2より必要なデータを、NOをキーにしてSUMPRODUCT関数にて取ってきて表示しています。
次の式をB1に入力しています
(=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2<>"鉄")*(sheet2!$D$2:$D$6=D2))
　　　A　　 　B　　 　C　　 　D　　 　E
1 NO 金額
2 01 50円
3 02 120円
4 03 110円

sheet3はsheet2より必要なデータを、NOをキーにしてSUMPRODUCT関数にて取ってきて表示しています。
次の式をB1に入力しています
(=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2"鉄")*(sheet2!$D$2:$D$6=D2))
　　　A　　 　B　　 　C　　 　D　　 　E
1 NO 金額
2 03 90円

回答No.2

KURUMITO

示されているＳＵＭＰＲＯＤＵＣＴ関数は少なくとも次のようにすることでしょう。

=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2<>"鉄")*(sheet2!$D$2:$D$6))

=D2を取った式にすることが必要ですね。

補足

KURUMITO様
早急なご回答をいただきありがとうございます。
私の記述式が間違っていたのですね。
式を修正して、当月のデータは問題なく出来たのですが
翌月の値が表示できなくなりました。

そこで、追加の質問ですが
翌月の値をsheet1に入力すると、sheet3とsheet4にも上記のように
値が表示するはずですが、両方のシートとも値がn/aとなり表示できません。
当月の値については、問題なく表示できるのですがなぜでしょうか。
式の値をいろいろ直しているのですが、うまくできません。

-----------------------------------------------------------
次月の集計データをsheet1に入力します。

(sheet1の入力状態)
　　　A　　 　B　　 　C　　 　D　　 　E　　
1 NO 社名 　 商品 購入日 金額
2 01 A商事 肉 2/3 50円
3 02 B電気 　野菜 2/10 70円
4 02 B電気 肉 2/12 50円
5 　　03 　 C工業 　魚 2/15 60円
6 03 　　 C工業 肉 2/17 50円
7 03 　　 C工業 鉄 2/20 90円
8 04 　　 D水産 魚 2/25 80円
9 04 　　 D水産 野菜 2/25 90円

sheet2を開いて、sheet1のNOを貼り付ける(A1～A9をコピー)とVLOOKUPにて
B1：D9を自動的に表示する。

(sheet2の入力状態)
B2=VLOOKUP(A2,sheet1!A2:B2,1,FALSE)

　　　A　　 　B　　 　C　　 　D　　 　E　　
1 NO 社名 　 商品 金額
2 01 A商事 肉 50円
3 02 B電気 　野菜 70円
4 02 B電気 肉 50円
5 　　03 　 C工業 　魚 60円
6 03 　　 C工業 肉 50円
7 03 　　 C工業 鉄 90円
8 04 　　 D水産 魚 80円
9 04 　　 D水産 野菜 70円

sheet3を開いて、NOを手入力すると(NO、01・02・03・04)とSUMPRODUCTにて
B2：B5を自動的に表示する。
(sheet3の入力状態)
B2=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2<>"鉄")*(sheet2!$D$2:$D$6))

　　　A　　 　B　　 　C　　 　D　　 　E　　
1 NO 金額
2 01 50円
3 02 120円
4 　　03 　 110円
5 04 　　 150円

sheet4を開いて、NOを手入力すると(NO、01・02・03・04)とSUMPRODUCTにて
B2：B5を自動的に表示する。

(sheet4の入力状態)
=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2="鉄")*(sheet2!$D$2:$D$6))

　　　A　　 　B　　 　C　　 　D　　 　E　　
1 NO 金額
2 01 0円
3 02 90円
4 03 0円
5 04 0円
-----------------------------------------------------------

回答No.1

keithin

ご質問の状況説明をもう一度ご自分でもよく目を通して、あちこち変なことを口走っている様子を確認してください。
●同じSheet3で、違うことを集計しようとしている
●Sheet3のＤ列に何を記入しているのか不明のまま、「Ｄ２に等しい」をSUMPRODUCTの計算の条件にしている
●Sheet2で、同じ02や03から違う結果をVLOOKUPできていることになっている
●「次の式をB1に入力しています」は、おそらくきっと、Ｂ２に記入していますといいたかったのかもしれない
●シート２のＢ列は社名なのに、掲示されたSUMPRODUCTの式はＢ列が商品になっている

それで結局何がしたいかというと？

１）「最初のSheet3」には、Noごとの、Ｃ列が鉄ではない行について、Ｄ列金額の集計をしたい？
シート３のＡ列にＮｏが記入しておいて
Ｂ２：
=SUMPRODUCT((Sheet2!$A$1:$A$6=A2)*(Sheet2!$C$1:$C$6<>"鉄"), Sheet2!$D$1:$D$6)

２）次のSheet3には、Ｎｏごとの、Ｃ列が鉄である行について、Ｄ列金額の集計をしたい
Ａ列にＮｏを記入しておいて
Ｂ２：
=SUMPRODUCT((Sheet2!$A$1:$A$6=A2)*(Sheet2!$C$1:$C$6="鉄"), Sheet2!$D$1:$D$6)