SUMPRODUCT 「#NUM」を回避して集計

質問

関数初心者です。Excel2000を使用しています。
前回の質問でSUMPRODUCT関数を使って複数条件の集計について質問しました。

この集計に関して、また新たに一つ問題が発生し模索中です。

集計したいのは複数条件
例）
2012/3/3 12:10:50　りんご　50

　日付　と　商品名　を条件とし　数値　の合計を出したいです。

前回の質問で回答して頂いた

=SUMPRODUCT((INT(B6:B12)=INT(B3))*(C6:C12=C3),D6:D12)

この式を使って集計をしたいのですが、
数値の範囲(D6:D12)にはすべて時間を分単位に変換するよう

=HOUR(N4-O4)*60+MINUTE(N4-O4)

の式が組み込まれていて、必ず１ヶ所に「#NUM」が表示されます。
この「#NUM」があるが為に集計結果がすべて「#NUM」となってしまいます。

どちらの式でも =(IF(ISERROR～ 等を試してみましたが、やはりうまくいきませんでした。
勉強不足の為、使い方の認識が根本的に違っているのかもしれません。

希望としては前式
=SUMPRODUCT((INT(B6:B12)=INT(B3))*(C6:C12=C3),D6:D12)
に「#NUM」を回避（無視）して集計出来るような式を組み込んで処理したいのです。

ちなみに検索範囲共は日々不規則に変化し上書きすることがないため
(D6:D1000)くらいで範囲設定をしておきたいのです。

説明がわかりにくくて申し訳ありません。
アドバイスをよろしくお願いします。

回答No.2

keithin · ベストアンサー

とりあえず出来る方法としては
=SUM((INT(B6:B1000)=B3)*(C6:C1000=C3)*IF(ISNUMBER(D6:D1000),D6:D1000,0))
をCtrl+Shift+Enterで入力すると，できます。

もちろん既に寄せられているアドバイスの通り，Ｄ列のエラーを事前に解消しておくのが最適なのは言うまでもありません。
また計算できることが目的じゃなくSUMPRODUCT関数が使いたいご質問だったときは，他の方のアドバイスをお待ち下さい。

お礼

ご回答ありがとうございます。

上記の式で完璧な動作をしてくれました。

SUMPURODUCTにこだわっていたのではなく、Excel2000でSUMIFSが使えず、複数条件で検索するとSUMPRODUCTを勧めるサイトが多かったために一番有効なのだと思い込んでおりました。

普段全く関数を使う機会がないので戸惑っておりましたが、たくさんの方に回答して頂きとても感謝しております。

直接的に解決へと導いて下さいましたkeithinさんの回答をベストアンサーとさせて頂きます。

本当にありがとうございました。

回答No.3

KURUMITO

D列には
=HOUR(N4-O4)*60+MINUTE(N4-O4)
のデータが有ってそれの集計をするとのことですか？
それにしても上の式は正しいのですか？
N4やO4のデータは時間の入ったデータになっているのですね。でしたら次のような式にしてエラーの表示を無くすようにすることでしょう。
例えば
=IF(COUNT(N4:O4)<>2,"",IF(N4>=O4,HOUR(N4-O4)*60+MINUTE(N4-O4),0))
又は
=IF(COUNT(N4:O4)<>2,"",IF(N4>=O4,HOUR(N4-O4)*60+MINUTE(N4-O4),HOUR(O4-N4)*60+MINUTE(O4-N4)）)

お礼

ご回答ありがとうございます。

D列のエラーを表示しないようにすることが一番の近道だと思います。
諸事情によりなるべくD列の式には手を加えず解決をしたかったのですが、最終的にその方法も試行してみました。

結果は惨敗でしたが(笑)

教えて頂いた式も大変参考になりました。
今後に活かしたいと思います。

ありがとうございました。

回答No.1

edomin7777

まずは、

> 必ず１ヶ所に「#NUM」が表示されます。

これの原因を調べ、対策を取る方が簡単です。

> どちらの式でも =(IF(ISERROR～ 等を試してみましたが、やはりうまくいきませんでした。
> 勉強不足の為、使い方の認識が根本的に違っているのかもしれません。

どうやって記載したのかが書かれていないので、違っているかどうかも判断できません。
でも、間違っているのは「使い方」ではなく、エラーをそのままにしておくという「考え方」ですよ。

お礼

ご回答ありがとうございます。

ご指摘はごもっともです。
いろんなサイトを参照し試行していたので経緯を書くとだいぶ長くなってしまうと思い割愛させて頂きました。

データ参照範囲は別の人が作った式だったので、なるべく手をつけずに解決する方法を模索しておりました。

まずは、エラーを出さないようにすること。
この考え方はしっかり今後に活かしたいと思います。

ありがとうございました。