SUMPRODUCT複数条件カウントで0が返る

質問

winXP,office2002

エクセルにて、作業一覧シートから「担当者」と「〆切日」別で進捗一覧を作りたいとおもい、
SUMPRODUCT関数を使用したところ、全て0を返してしまいます。

　　A　　　　　B　
1　担当者 山田
2　〆切日　作業数
3　1/7（月）　=SUMPRODUCT(((別シート!$F$4:$F$109)="山田*")*((別シート!$G$4:$G$109)=$A3))
4　1/8（火）　=SUMPRODUCT(((別シート!$F$4:$F$109)="山田*")*((別シート!$G$4:$G$109)=$A4))
5

日付の書式はm/d(aaa)です。
別シートF行は担当者名が入力されております。空欄もあります。
別ソートG行は日付m/d(aaa)が入力されております。空欄もあります。
担当者は連名の場合もあるため、"山田*"にしております。

なお試しに、countifでそれぞれ担当者と〆切日の単条件をカウントすると、正常に結果を返します。

いろいろと検索し、試してみましたが、どうしても0になってしまいます。
どこが間違っているのでしょうか？
よろしくお願いします。

回答No.2

kagakusuki · ベストアンサー

>比較演算子ではワイルドカードは使用できないからです。

というの点に関しては、回答No.1様の仰る通りなのですが、

FIND("山田",別シート!$F$4:$F$109)

としたのでは、担当者名が例えば「小山田」となっているデータに関しても、「山田」のデータとして一緒にカウントされてしまいます。
　ですから、

>担当者は連名の場合もあるため、

という場合には、連盟となっている複数の担当者名の間の区切り方が、どの様になっているのか、という事が重要になります。
　例えば、「山田さん」と「佐藤さん」の連名ですと、「山田,佐藤」の様に「,」で区切られているのか、或いは、「山田 佐藤」の様に半角のスペースで区切られているのか、等々によって、若干異なる関数を使用せねばなりません。
　例えば、複数の担当者名の間が必ず「,」で区切られている場合には、B3セルに入力する関数を次の様なものにされると良いと思います。

=IF(OR(ISERROR(1/DAY($A3)),$B$1=""),"",SUMPRODUCT(ISNUMBER(FIND(","&$B$1&",",","&別シート!$F$4:$F$109&","))*(別シート!$G$4:$G$109=$A3)) )

　又、複数の担当者名の間が必ず半角のスペースで区切られている場合には、B3セルに入力する関数を次の様なものにされると良いと思います。

=IF(OR(ISERROR(1/DAY($A3)),$B$1=""),"",SUMPRODUCT(ISNUMBER(FIND(" "&$B$1&" "," "&別シート!$F$4:$F$109&" "))*(別シート!$G$4:$G$109=$A3)))

　又、複数の担当者名の間が、半角のスペースで区切られている場合と、全角のスペースで区切られている場合と、「,」（半角）で区切られている場合と、「，」（全角）で区切られている場合と、「/」（半角）で区切られている場合と、「／」（全角）で区切られている場合の6パターンがある場合には、B3セルに入力する関数を次の様なものにされると良いと思います。

=IF(OR(ISERROR(1/DAY($A3)),$B$1=""),"",SUMPRODUCT((ISNUMBER(FIND(" "&ASC($B$1)&" "," "&ASC(別シート!$F$4:$F$109)&" "))+ISNUMBER(FIND(","&ASC($B$1)&",",","&ASC(別シート!$F$4:$F$109)&","))+ISNUMBER(FIND("/"&ASC($B$1)&"/","/"&ASC(別シート!$F$4:$F$109)&"/"))+>0)*(別シート!$G$4:$G$109=$A3)))

お礼

丁寧な解説ありがとうございました。
連名は「・」で区切られておりましたので、代用して使わせていただきました。

回答No.5

kagakusuki

回答No.3様は

>(FIND = 1) という論理式による論理値を使えば、後ろのほうの区切り方については何も考える必要がないということが重要になります。

と仰っておられますが、やはり区切り方は考えに入れなければなりません。
　何故なら、

=SUMPRODUCT((FIND("山田",別シート!$F$4:$F$109&"山田")=1)*(別シート!$G$4:$G$109=$A3))

や

=sumproduct((countif(indirect("別シート!$F"&row($4:$109)),"山田*")=1)*(別シート!$G$4:$G$109=$A3))

や

=SUMPRODUCT((FIND("山田",別シート!$F$4:$F$109&"★山田")=1)*(別シート!$G$4:$G$109=$A3))

では、連名が「佐藤、山田」となっている場合には、「山田」さんのデータとして扱われませんし、仮に、「山田屋」という担当者名があったりしますと、「山田屋」さんのデータも「山田」さんのデータとして扱われてしまいます。
　まあ、「山田」さんであれば、「山田■」という形式の名前は少ないと思われますが、「林」さん、「小林」さん、、「林田」さん、とか、「藤」さん、「内藤」さん、「藤本」さん、という組み合わせも考えられます。

>なお、この数式において、「山田*」→「*山田*」と書き換えれば、先頭が山田でないケース（「小山田」とか「佐藤さん、山田さん」とか）も条件に該当するものとして数えます。

　その場合、「小山田」さんや「山田屋」さんが担当者に含まれてさえいますと、「山田」さんが含まれていないデータであっても、「山田」のデータとして一緒にカウントされてしまいます。

回答No.4

MarcoRossiItaly

No.3 です。ごめんなさい、ミスをしたので、No.3 の 1 本目の数式を次のとおり訂正させてください。

=SUMPRODUCT((FIND("山田",別シート!$F$4:$F$109&"★山田")=1)*(別シート!$G$4:$G$109=$A3))

★でなくても、好きな文字でいいです。

これがないと、「別シート」の F 列が未入力かつ「別シート」の G 列が A3 セルの値に等しい場合に、「1」と数えてしまうので、少し問題です。失礼しました。

回答No.3

MarcoRossiItaly

＞>比較演算子ではワイルドカードは使用できないからです。
　というの点に関しては、回答No.1様の仰る通りなのですが、
　FIND("山田",別シート!$F$4:$F$109)
　としたのでは、担当者名が例えば「小山田」となっているデータに関しても、
　「山田」のデータとして一緒にカウントされてしまいます。

という点に関しては、回答 No.2 さんのおっしゃるとおりなのですが、

＞連盟となっている複数の担当者名の間の区切り方が、どの様になっているのか、
　という事が重要になります。

ということが言えるのは、ISNUMBER 関数が返す論理値を使って立式した場合に限られるのであって、(FIND = 1) という論理式による論理値を使えば、後ろのほうの区切り方については何も考える必要がないということが重要になります。

=SUMPRODUCT((FIND("山田",別シート!$F$4:$F$109&"山田")=1)*(別シート!$G$4:$G$109=$A3))

上式では、「山田」が 2 回出てくることに注意してください。2 回目の「山田」を付けていない数式の場合は、「別シート」の F 列に「山田」という文字列が含まれないセルが 1 つでも存在すると、エラーになってしまいます。

ワイルドカードが使えるのは、一部の関数だけです。多くの関数または数式においては、使えません。

COUNTIF で複数条件とすることは、一応、次のようにして可能です。つまりワイルドカードが使えます。ただ、COUNTIF と配列という組み合わせの相性の問題で、ROW と INDIRECT を入れるという工夫をしているので、割と難しい数式です。

=sumproduct((countif(indirect("別シート!$F"&row($4:$109)),"山田*")=1)*(別シート!$G$4:$G$109=$A3))

なお、この数式において、「山田*」→「*山田*」と書き換えれば、先頭が山田でないケース（「小山田」とか「佐藤さん、山田さん」とか）も条件に該当するものとして数えます。

お礼

ワイルドカードでできる方法も教えていただきありがとうございます。
便利ですが、よく考えて使わないと、混乱しますね（笑）
いろいろと勉強になりました。

回答No.1

mu2011

比較演算子ではワイルドカードは使用できないからです。
数式が長くなるが、次の様な感じです。
=SUMPRODUCT(ISNUMBER(FIND("山田",別シート!$F$4:$F$109))*(別シート!$G$4:$G$109=$A3))

お礼

ワイルドカードは使用できなかったんですね・・；
検索した例題では使用されていたので、そのまま使用したのですが、
できないわけですね（笑）

担当者が連名でなく、かつ漢字のかぶりが無い場合には、使用してみます！