SUMPRODUCT関数で文字が入っているセルの個数を集計したい

先般からSUMPRODUCT関数について質問させていただいている者です。
今度の質問は、Excel2013を使い、以下の集計シートの○の部分を集計したいです。

＜集計シート＞
A列　　　B列　　　　　C列　　　D列
日付　　　1件　　　　　2件　　　3件以上
12/1　　　○件　　　　○件　　　 ○件　　　　→　　2件　　3件　　1件
12/2　　　○件　　　　○件　　　 ○件　　　　→　　1件　　2件　　0件
12/3　　　○件　　　　○件　　　 ○件
12/4　　　○件　　　　○件　　　 ○件
12/5　　　○件　　　　○件　　　 ○件
12/6　　　○件　　　　○件　　　 ○件

＜データシート＞
A列　　　　B列　　　　　C列　　　　　D列　　　　　E列
日付　　　支店1　　　　　支店2　　　　支店3　　　　支店4
12/1　　　新宿支店　　　東京支店　　　品川支店　　　新橋支店
12/1　　　東京支店　　　上野支店
12/1　　　上野支店　　　　　　　　　　新宿支店
12/1　　　新宿支店
12/1　　　品川支店
12/1　　　新橋支店　　　品川支店
12/2　　　　　　　　　　新橋支店　　　品川支店
12/2　　　　　　　　　　　　　　　　　東京支店


上記のようなデータシートがあり、集計シートに件数を入れていきたいのですが、
毎度ながら手こずっています。
それぞれ、どのような数式が当てはまるか、ヒントをいただければ幸いです。
B2=
C2=
D2=

当方では、
D2=SUMPRODUCT((('データシート'!$A$2:$A$10000)=A2)*(('データシート'!$B$2:$B$10000<>"")+('データシート'!$C$2:$C$10000<>"")+('データシート'!$D$2:$D$10000<>"")+('データシート'!$E$2:$E$10000<>"")>=3)))

としてみましたが、うまく表示できません。
SUMPRODUCT関数について応用ができず、不甲斐ないのですが、ご教授のほど、よろしくお願いいたします。

No.4 ベストアンサー 回答者： fujillin 回答日時： 2019/12/05 15:46

No3です

タイトルに「SUMPRODUCT関数で」とあったので、SUMPRODUCTでの回答例が良かったのかも知れませんね。

・・・ということで、作り直してみました。
No3とほぼ同じ内容ですが、SUMPRODUCT関数で書き直せば、こんな感じになると思います。
同じく、Ｂ2セルに
=SUMPRODUCT((データシート!$A$2:$A$10=$A2)*(COUNTIF(OFFSET(データシート!$B$2:$F$2,ROW(A$2:A$10)-2,0),"<>")=COLUMN(A1)))

※　ひとまず、範囲は2～10行目までの小さなものにしてあります。

この回答へのお礼

検証に時間がかかってしまい御礼が遅れ失礼しました。
皆さんのご厚意に感謝しています。
ベストアンサーは悩んだのですが、こちらにさせていただきます。
有り難うございました。

お礼日時：2019/12/09 10:07

No.7 回答者： d-q-t-p 回答日時： 2019/12/06 17:04

> 仰る通り、段階を踏んで計算すればできることでした。

いえ特に段階を踏めばいいなんて回答をした覚えはないんですが……
書いた数式で計算できるはずです。

この回答へのお礼

まだ検証ができておらず、御礼のご返事ができず失礼しました。
作業シートを使用せずにできる方法があることが分かりました。
これから一つ一つ検証していきます。有り難うございました。

お礼日時：2019/12/09 10:11

No.6 回答者： d-q-t-p 回答日時： 2019/12/06 09:40

使う関数を限定するなんて無意味なこだわりですね。

「1件」の数なら
=SUM(N(MMULT((データシート!$A$2:$A$10=$A2)*(データシート!$B$2:$E$10<>""),{1;1;1;1})=1))

最後の「=1」のところを変えれば 他の件数も出るでしょう。

個人的には そもそもこれを数式だけで計算しようとすること自体が
どうかと思います。クエリで「支店1」～「支店4」をアンピボット
してから日付ごとにグループ化してカウントすれば 何てことのない
計算です。

この回答へのお礼

d-q-t-pさま

コメントいただき、有り難うございました。
特別SUMPRODUCT関数を使用することに拘ったつもりはなく、
私自身がこの関数を使用すれば、この数式だけで完結すると思い込んでいましたので、
タイトルに書かせていただいただけです。
仰る通り、段階を踏んで計算すればできることでした。

お礼日時：2019/12/06 16:57

No.5 回答者： tom04 回答日時： 2019/12/05 16:26

No.2です。

＞データシートのX列に"完了"または"予定"のフラグがあり、"完了"のみを抽出した場合・・・

今回も一気にやるのではなく、作業用の列を使う方法です。
↓の画像のように作業用の列をY列にしてみました。

作業列Y2セルに
=IF(X2="完了",A2&"_"&COUNTA(B2:E2),"")

という数式を入れています。

そして、「集計シート」のB2セルの数式も少し変わります（作業列が変わったため）
=COUNTIF(データシート!$Y:$Y,$A2&"_"&COLUMN(A1))

として隣りのC2セルまでフィル＆コピー！

D2セルの数式が大幅に変わります。
D2セルは
=COUNTIFS(データシート!A:A,A2,データシート!X:X,"完了")-(B2+C2)

という数式になります。

前回同様、B2～D2セルを範囲指定　→　D2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　SUMPRODUCT関数にこだわるのであれば、D2セルに使えます。
D2セルは
=SUMPRODUCT((データシート!A$2:A$1000=A2)*(データシート!X$2:X$1000="完了"))-(B2+C2)

といった感じでしょうか。

※　SUMPRODUCT関数は配列数式になりますので、極端に広い範囲を指定するのはおススメしません。
計算速度が大幅に落ちてしまいます。
とりあえず1000行程度の範囲にしていますが、5000行位ならストレスは感じないと思います。m(_ _)m

この回答へのお礼

tomo04さんのご回答が、関数初心者の私としては一番わかりやすく、実用的な方法をご提示いただきました。
有り難うございました。

お礼日時：2019/12/09 10:08

No.3 回答者： fujillin 回答日時： 2019/12/05 11:44

こんにちは

すでに指摘が出ていますが、SUMPRODUCT関数だけでなく関数を組み合わせる場合に、それぞれの部分で何を計算しているかをきちんと把握していないと望む結果にはなりません。
特に、配列計算の場合は1セルで複雑な結果を求められるというメリットはあるものの、式がそれ相応に複雑になるのと、内部的には無駄な計算を行うために負荷が大きくなるというデメリットがあります。

さらに、関数を組み合わせる場合、配列計算に対応している関数とそうでないものがあるので、結局、各々を確認しながら式を組まなければなりません。
例えば、下に例として挙げた式のうちCOUNTIFで計算している内容はCOUNTA関数で十分なはずの内容ですが、COUNTAだと配列計算をしてくれないので、敢えてCOUNTIFにしてあるといったようなことが起こります。
この計算の過程をそのまま作業列を利用して計算する方法を考えてみれば、一例として、No2様の回答のような方法が考えられます。

ご質問を見ると、No1様がご指摘のように式の内容を十分に理解することなく、関係のあるセル範囲を組み合させているように思われますが、一気に結果を求めようとせずに、まずは、結果を算出するまでの過程を分解して、作業列等を利用しながら順次算出してゆく方法をとることをお勧めします。
この考え方のメリットは、計算式が圧倒的にわかりやすくなることや、無駄な計算を減らすことができ負荷を軽減できることなどが挙げられます。
（作業列は非表示にするなどで、見た目は直接算出した場合と同じにすることもできます）


最後に、ご参考までに、ご質問内容に近い計算式を・・・
集計シートのＢ2セルに
=SUM(INDEX((データシート!$A$2:$A$10=$A2)*(COUNTIF(OFFSET(データシート!$B$2:$F$2,ROW(A$2:A$10)-2,0),"<>")=COLUMN(A1)),,))
の式を入れて、オートフィルします。

ただし、上式のままだと、Ｂ列には1件の、Ｃ列には2件の、Ｄ列には3件の・・・と順に数がカウントされます。
少し修正すれば、ご質問の内容になりますが、ヒントとしてここまでとしておきます。

この回答へのお礼

fujillinさん

毎度お世話になっております。ご丁寧なコメント、本当に有り難うございます。
その通り、一気に結果を求めようとしていました。
「結果を算出するまでの過程を分解して、作業列等を利用しながら順次算出してゆく方法」
これが、一見回りくどいようですが近道なのだと理解しました。
当方、締め切りが迫っており、焦りもございましたので。。。これは言い訳です。

あと、ご参考としてご提示いただいた計算式では、OFFSETやCOLUMNといった
使用したことがない関数が並べられており、一度に理解不能でした。
これも、過程を分解していく中で得られるものなのでしょうね。
すぐに使いこなせるとは思えませんが、「これが理解できれば一歩進める」という気持ちになれました。
頑張ります！
まずは御礼申し上げます。

お礼日時：2019/12/05 13:55

No.2 回答者： tom04 回答日時： 2019/12/04 18:18

こんばんは！

質問文だと「集計シート」の12/2のC列（2件の列）は「1」ですよね？

一例です。
↓の画像のように作業用の列を設けてみました。

尚、「データシート」のB～E列には最低1つ以上のデータが入る！という前提です。
（A列にシリアル値があり、B～E列すべてが空白だとお望みの結果になりません）

「データシート」のF列を作業列とし、F2セルに
=IF(A2="","",A2&"_"&COUNTA(B2:E2))

という数式を入れ下へフィル＆コピー！

「集計シート」のB2セルに
=COUNTIF(データシート!$F:$F,$A2&"_"&COLUMN(A1))
という数式を入れ、隣りのC2セルまでフィル＆コピー！

D2セルには
=COUNTIF(データシート!A:A,A2)-(B2+C2)

という数式を入れ、B2～D2セルを範囲指定　→　D2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

※　細かいエラー処理はしていません。m(_ _)m

この回答へのお礼

tom04さん

コメント有り難うございました。
No.3の方がご指摘いただいておりますが、作業列を作成するなどして、過程を分解していくことが大事だと認識しました。
また、私のようなものに、わざわざ作業シートを作成されてご教授くださいまして恐縮です。
ぜひ参考にさせていただければと存じます。

なお、一点ついでながら質問させていただけますでしょうか。
私が提示したデータシートですが、こちらは抽出後のデータとなっていまして、たとえばデータシートのX列に"完了"または"予定"のフラグがあり、"完了"のみを抽出した場合のデータ集計を行いたい、というのが前提でした。
そういった事もあり、SUMPRODUCT関数を利用した方が良いのかな、と思ったのですがなかなか難しいですね。

お礼日時：2019/12/05 13:37

No.1 回答者： 銀鱗 回答日時： 2019/12/04 16:38

そもそもSUMPRODUCT関数がどのようなことをするための関数かご存じないような質問ですね。

商品名　単価（円）　個数
バナナ　　130　　　10
リンゴ　　200　　　20
イチゴ　　150　　　30
ミカン　　120　　　15

のような表の合計の値段を求めるようなことをする関数です。
　130×10
　200×20
　150×30
✚120×15
￣￣￣￣￣
こんな事をするために
　SUMPRODUCT(単価の範囲，個数の範囲)
とするのです。
「単価」に相当する範囲に項目の有無を示す「1」か「0」、
「個数」に相当する範囲に何らかの条件の結果として「1」か「0」を入れれば、
”あり” ”なし” の判断ができ、”あり” の数だけを数えることができる、
…と、応用しているだけですね。

・・・
自分ならD2セルには、
　（全体の数）－（「B2」+「C2」）
とするかな。

この回答へのお礼

銀鱗さん
コメント、有り難うございました。
今回、初めてSUMPRODUCT関数に出会い、配列数式の難しい概念に頭を悩ませていました。
しかしご指摘のように私自身の基礎ができておらず、ご迷惑をおかけしております。
ネットで調べてみてはトライアンドエラーを繰り返し、この質問板で皆さんのお力をお借りしながら、
少しずつですが前進してきました。
今後ともよろしくお願いいたします。

お礼日時：2019/12/05 13:24