エクセル サムイフ関数の合計範囲にエラー値（#VALUE!）がある場合に無視して集計する関数

質問

宜しくお願い致します。
エクセルのＡ列に個々の商品名を、Ｂ列に売上個数を表示していて、Ｃ列に検索条件に使う為に商品名を、Ｄ列にサムイフ関数の結果を表すようにしています。
（Ｄ列には、Ｃ列の商品名と同じ物をＡ列から探して、（Ｂ列の）売上個数の合計を出すという事です）
そして、Ｂ列の売上個数は他の数式を使って持ってきているので、数字の時もあればエラー値（#VALUE!）の時もあります。

質問させて頂きたいのは、サムイフ関数の検索条件で指定した商品の売上個数（Ｂ列）のどれか１つでもエラー値（#VALUE!）があったら、計算結果もエラー値（#VALUE!）になってしまうので、エラー（#VALUE!）がある場合はそのセルを無視した売上個数の合計を出したくて、そのエラー値（#VALUE!）が数値に更新された時はその売上個数も集計されるような関数を作りたいのですが、ご存知の方ご指導お願い致します。

過去の質問を調べてみましたら、サム関数の場合はISNUMBERというのを使用した解決方法がありましたので、自分でサムイフ関数の場合を試してみましたが、上手く行きませんでした。
http://okweb.jp/kotaeru.php3?q=883905
（QNo.883905　04/06/07 15:59）
使用しているＯＳはＸＰ、エクセルは2003です。
わかりずらい質問文ですみません。宜しくお願い致します。

回答No.3

papayuka · ベストアンサー

#1です。

詳しく検証してないですが、

=SUMPRODUCT((A1:A10=C1)*IF(ISERROR(B1:B10),0,B1:B10))

これで、Ctrl+Shift+Enter でそれっぽいのが出ました。

あとはB列をマクロで一括書き換えするとか。

Sub Test()
Dim r As Range, f As String
For Each r In Selection
　If r.HasFormula Then
　　f = Mid(r.Formula, 2, Len(r.Formula))
　　r.Formula = "=if(iserror(" & f & "),0,(" & f & "))"
　End If
Next r
End Sub

お礼

こんばんわ。再びのご回答ありがとう御座います。
上の関数をＤ列に入れたらあっさりと数値が出ました。
Ｂ列のエラー値が他の場合はどうか試してみましたが、#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!の全てで希望どうりの結果になりました。（参照するＢ列がエラー値の場合はそのセルを無視して合計して、数値の場合は集計される）
非常に助かりました。
本当にありがとう御座いました。

回答No.2

pachikuri

＞（最後の手段として１つ１つＢ列の数式を変えていく方法をとります。）
Ｂ列の式を書き直すのが大変なら，Ｃ列のところに１列挿入して
=IF(ISERROR(B1),0,B1)
などとすればどうでしょう。

印刷に影響するようなら，列幅を小さくして・・・・

お礼

pachikuriさん。こんばんわ。
早速のご回答ありがとう御座います。
新しく一列挿入して、Ｂ列をふるいにかける方法もまったく思いつきませんでした。
こちらもＢ列のエラー値が他の場合はどうか試してみましたが、全てで希望どうりの結果になりました。
ポイントの方は先着順ということでご了承下さい。
非常に助かりました。
本当にありがとう御座いました。

回答No.1

papayuka

> Ｂ列の売上個数は他の数式を使って持ってきているので、
> 数字の時もあればエラー値（#VALUE!）の時もあります。

このB列の関数でエラーなら 0 を返すようにするとか。

=IF(ISERROR(元の式),0,元の式)

お礼

papayukaさん。こんばんわ。
早速のご回答ありがとう御座います。
サムイフ関数でなく、Ｂ列の数式を変えることはまったく思いつきませんでした。
しかし、Ａ列Ｂ列が数百行あり、Ｂ列の数式の参照先がバラバラなので数式のコピー＆ペーストが出来ないので、他に方法があればお願い致します。
（最後の手段として１つ１つＢ列の数式を変えていく方法をとります。）
ありがとう御座いました。