SUMPRODUCTに3つ以上の条件

質問

A列に男女別、B列に年齢別、C列に住所、D列に部署別のデータが1万人以上、入っています。
１つのセルに、関数で港区、男性、30代の人数のデータを抽出したい。
SUMPRODUCTに条件の2つまでは正しく抽出されるが、3つめの年代別がうまくいきません。
また、そのほかに
E-1セルに"男女別"、F-1セルに"年齢"、G-1セルに"住所"、H-1セルに"部署名"、I-1セルに
人数の項目を作成し、E～Hに条件を入れれば、I-1のセルに、人数を出したい場合の作り方を
教えてください。よろしくお願いします。

回答No.2

Cupper-2 · ベストアンサー

まず、質問者さんはどのように SUMPRODUCT関数を使用しているのでしょう。

条件を
　SUMPRODUCT関数の中でかけ算しているか、
　パラメータとして渡してSUMPRODUCT関数にかけ算させているか
で動作が変わってきます。

■本題
３つ以上の条件を使う場合は、関数の中でかけ算しないと正しい結果を得られません。
野郎で成人、北海道の総務なら

　=SUMPRODUCT((A1:A10="男")*(B1:B10=>"20")*(C1:C10="北海道")*(D1:D10="総務"))

こんな感じ。

てか、作り方というか、２つまでの条件式の意味を理解されていないと思うんですよ。
＞E-1セルに"男女別"、F-1セルに"年齢"、G-1セルに"住所"、H-1セルに"部署名"、I-1セルに
＞人数の項目を作成し、E～Hに条件を入れれば、I-1のセルに、人数を出したい場合の作り方を～
理解されているなら、こんな質問はしませんからねえ。
（質問をするのであれば、自身がどれだけ理解しているかを示さないと
　答えを見ても呪文がならんでいるだけにしか受け止めることができませんよ。
　それって、目先の問題は解決するけど、ちょっとでも条件が変わると
　とたんに難題に変わってしまいます。
　本当に問題を解決したいのであれば
　＞条件の2つまでは正しく抽出されるが～
　という書き方はやめておきましょう。
　回答者さんが勘違いして回答することがあります。）

悪いことは言いません。
とりあえず、使い方をしっかり 【理解】 して考えてから行動を起こしましょう。

　I1=SUMPRODUCT((A1:A10="男")*(B1:B10=>"20")*(C1:C10="北海道")*(D1:D10="総務"))
　I1=SUMPRODUCT((A1:A10=E1) * (B1:B10=>F1) * (C1:C10=G1) * (D1:D10=H1))

お礼

本当に助かりました。関数の使い方を勉強します。

回答No.4

tom04

No.3です！
補足の数式
＞=SUMPRODUCT(($A$2:$P$10000="男 ")*($G$2:$G10000="東京都港区")*COUNTIF(B2:B1000,">=20")-COUNTIF(B2:B1000,">=30"))

については間違いが多すぎます。
基本的にSUMUPRODUCT関数は　列単位で「TRUE」=1 か「FALSE」=0　かを判断し
その行ごとの掛け算をプラスしたものになりますので、
数式内の列の行数は合わせてやる必要があります。
すなわち上記数式ですとA列が「男」・G列が「東京都港区」の場合その行は「1」となります。

ところでお考えの数式では
SUMPRODUCT関数内にCOUNTIF関数を入れているのでエラーになるか、全く意味のない数式になってしまいます。

おそらく
=SUMPRODUCT((A2:A10000="男")*(G2:G10000="東京都港区")*(B2:B10000>=20)*(B2:B10000<30))
といった感じをやりたかったのでしょうか？

今一度、SUMPRODUCT関数の使い方をネットでも検索できるので
確認してみてください。m(_ _)m

回答No.3

tom04

こんばんは！
＞3つめの年代別がうまくいきません・・・
とあるのですが、住所に関してはD列データは「港区」が含まれている住所が入っているのではなく、
単に「○○区」のようにH1セルに入力するデータそのものが入っているのでしょうか？
そして、F1セルには10・20・30・・・のように10の倍数が入る訳ですよね？

そうだとして
=SUMPRODUCT((A1:A10000=E1)*(B1:B10000>=F1)*(B1:B10000<F1+10)*(C1:C10000=G1)*(D1:D10000=H1))
ではダメですか？

尚、SUMPRODUCT関数は配列数式になってしまいますので、データが1万以上あるとPCにもかなり負担になると思います。

そこで余計なお世話かもしれませんが、作業用の列を使ってみてはどうでしょう？

作業列としてA列を1列挿入します（元データが右へ1列ずつずれます）
データは2行目以降にあるとして、A2セルに
=IF(AND(B2=$F$1,C2>=$G$1,C2<$G$1+10,D2=$H$1,E2=$I$1),1,"")
という数式を入れ、A2セルのフィルハンドルでダブルクリック！
結果のJ1セルには単に
=SUM(A:A)
としておきます。

もう一つ余計なお世話かもしれませんが、D列住所が「東京都港区・・・」のようになっている場合は
A2セルの数式を
=IF(AND(B2=$F$1,C2>=$G$1,C2<$G$1+10,COUNTIF(D2,"*"&$H$1&"*"),E2=$I$1),1,"")
にします。

参考になりますかね？
長々と失礼しました。m(_ _)m

補足

質問が良くなかったようで済みません。

まず、下記のように作成しましたが、3つめの年代別の20代をCOUNTIFを入れましたがだめでした。

=SUMPRODUCT(($A$2:$P$10000="男 ")*($G$2:$G10000="東京都港区")*COUNTIF(B2:B1000,">=20")-COUNTIF(B2:B1000,">=30"))

回答No.1

edomin7777

> SUMPRODUCTに条件の2つまでは正しく抽出されるが、3つめの年代別がうまくいきません。

今現在、どういう関数を記述しているのかを書いた方が早いと思うが…。
（「うまくいきません。」だけで理解できる人はいません。）

お礼

言われてみればそうでした。失礼しました。