いま一歩の所まで来ていると思うのですが・・・詳しい方お教え下さい。

質問

いま一歩の所まで来ていると思うのですが・・・詳しい方お教え下さい。
SUMPRODUCT関数で対象範囲に空白行が含まれている場合の使い方で困っています。
例えば、会員名簿でＥ列にDATEDIFで年齢が表示されています。
Ｋ列に脱会した日付を記入していますので、脱会していなければＫ列空白セルとなっています。

　　　Ａ　　　Ｅ　　　　　Ｋ　
１　佐藤　　６２歳　　　脱会の日付
２　山田　　５９歳　　　　
３　伊藤　　３２歳　　　脱会の日付
４　山口　　６６歳
５　小沢　　２８歳
６　
７

脱会していない会員（空白の行）のうち６０歳以上と６０歳以下の人数を出したいのですが
６０歳以下を
=SUMPRODUCT((K4:K7="")*(E4:E7<60))
６０歳以上を
=SUMPRODUCT((K4:K7="")*(E4:E7>59))
とすると、まだ未記入の６行７行のＥ列のセルもカウントしてしまいます。
未記入のセル（これから入会される人の分）にする場合
上記の条件で、未記入のセルをカウントしない方法をご教授いただければと思います。
上記の関数の使い方自体間違っているかも知れませんが、よろしくお願いいたします。

回答No.2

prius770c · ベストアンサー

回避方法は、氏名や年齢が空白でないという条件を加える方法（既出）
もしくは、年齢が0歳以上という条件を加える方法などがあります。
=SUMPRODUCT((e4:e7<60)*(e4:e7>0)*(k4:k7=""))

SUMPRODUCTの場合だけでなく複数関数を使用したときなどで思い通りに動いていなかったら一度その式を分解して自分の思い通りの結果になっていない部分を特定するような癖をつけると問題点を洗い出しやすいですよ。（今回の場合は、60未満を判断する部分に誤りがあった）

以下は、細かい話なので余裕があればご参考までに

空白セルが数えられてしまっている理由は、簡単に言ってしまうと「空白も60未満」とエクセルが判断しているからです。
試しに、「=e7<60」と打ってみてください、「true」と値が出てくるはずです。

そして、SUMPRODUCTはtrueを「１」としてfalseを「０」として認識します。
そのため、年齢が空白=true=1で退会の日付が空白=true=1と認識されてしまっています。
SUMPRODUCTは（今回の場合は）、(e4*k4)+(e5*k5)+・・・(e7*k7)の結果を出します。

年齢条件と退会日付の有無でともに条件に合致している場合はtrue*true=1*1=1
年齢条件と退会日付の有無でともに条件に合致していない場合はtrue*false=1*0=0
とそれぞれ計算をしてその結果を足し上げているので合致している場合のみが1として数えられているのでカウントしているように見えているのが、SUMPRODUCTを使用した複数条件のカウント方法の正体です。

細かい話ですがSUMPRODUCTは、sumifsが無かった頃に合計したい条件分岐が複数ある場合に使われていますが、本当は配列の積和を計算するための関数で下記のようにA～Cの商品を見積もった場合に合計はいくらになるのかといった感じで使います。
それぞれ、まずA～Cの合計金額をだしてさらにそれを合計すると言った事をせずに自分でA~Cの合計金額（積）を計算してその合計（和）を出すです。

A B C
商品　単価　数量
2 A　　　100　10
3 B　　　200　10
4 C　　　100　10

=sum(b2*C2)+(b3*C3)+(b4*C4)としないで
=SUMPRODUCT(b2:b4,c2:c4)で同様の計算をします。
例では、sumでも面倒ではありませんがこれが3商品から100商品などになると・・・
でも、UMPRODUCTなら指定している範囲を広げるだけです。

まあ、あまりにも範囲が大きいと重たくなってしまいますがｗ

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/sumproduct.htm

だらだら書きましたが、上記のページが参考になるかと思います。

お礼

いろいろ丁寧な解説をありがとうございます。
SUMPRODUCT 使う機会が多いので、今回のように空白を０としてカウントしてしまうという性質を知ることが出来て、とても勉強になりました。
他にも、回避方法をご教授いただき、今更ながらエクセルの奧の深さに感心しています。
今後も参考にさせていただきます。

前回答者tomo04様の方法（考え方）も含め、解決出来ました。
ありがとうございました。m(_ _)m

回答No.1

tom04

こんばんは！

もう一つ、A列かE列が「空白以外」を付け加えてはどうでしょうか？

=SUMPRODUCT((K1:K7="")*(E1:E7<60)*(A1:A7<>""))

こんな感じです。

以上、他に良い方法があればよみながしてくださいね。m(__)m

お礼

早速ご回答いただき、ありがとうございました。m(_ _)m
こういう方法があったのですね！！解決しました。
条件を増やす方法までは思いつかなかったです。
ありがとうございました。