エクセル：#N/Aを除いて除算の和を計算したいのですがわかりません

エクセルに詳しい方、どうか教えてください。お願いします >-<"

出したい数値：表のなかで「〇」がついた商品を、店舗ごとに決まった予算でそれぞれ発注した時の商品の個数の和
問題点：仕入価格が0円の商品があるとき、計算上で個数の和がエラーになってしまう

「〇」が入っている商品をそれぞれ予算で割った数値を足す、なおかつ#N/Aのエラーを除いて足したい。こんな時、3列目にはどんな関数を入れたらエラーなく全て表示されるようになるのでしょうか？
SUMIFSとかSUMPRODUCTなのかなと思うのですが、初心者なので全然わかりません。
よろしくお願いします。

質問者からの補足コメント

• 説明が解りづらくて申し訳ございません
  C1をB4:B8までで除算した和の値になります
  
  B列の値で0のものがなければ
  C3に=SUMPRODUCT(C2/C4:C8*(B4:B8="〇"))
  で数字は出せたのですが
  割る数が0でエラーになるもののみ除外して足すことができるのかが
  わからない状態です。

  No.2の回答に寄せられた補足コメントです。 補足日時：2020/01/05 18:32

No.5 ベストアンサー 回答者： moto_koukousei 回答日時： 2020/01/06 21:28

どうしてそういう計算をしたいのかが分かりませんが、計算式だけならば、画像のようなのでも出来ます。

　
D3のセルの計算式
　=SUMPRODUCT(($B4:$B15>0)*1,INT(D$2/($B4:$B15+($B4:$B15=0))))
googleのスプレッドシートのやり方は分かりません。
EXCELの場合です。

この回答へのお礼

質問で出した例もあくまでも例でして、仕事上もっと込み入ったことでこういった計算が必要でした。何がしたいのか分からないというご意見はごもっともですが、親身に答えて頂きありがとうございました。なんとか解決致しました。

お礼日時：2020/01/07 12:54

No.4 回答者： d-q-t-p 回答日時： 2020/01/06 11:25

画像と補足の式のセル番地が合っていないようです。

そもそもその式で「#N/A」エラーは出ませんね。細かいことを
いうなら 画像では「〇（ゼロ）」ですが 数式では「○（まる）」
になっています。

=ROUNDUP(C2/SUMIFS($B4:$B8,$B4:$B8,"<>0",C4:C8,"〇"),0)

こうでないならもう少し最初の最初から説明し直すべきかと思い
ます。

No.3 回答者： mike_g 回答日時： 2020/01/05 22:14

[No.2補足]へのコメント、

》 C3に=SUMPRODUCT(C2/C4:C8*(B4:B8="〇"))
》 で数字は出せたのですが
嘘でしょォ～！「B4:B8」に〇なんて見当たらないしィ～！
「C4:C8」と「B4:B8」を入れ替えても、です。
参考までに、D3、E3 がそれぞれ 20、56 になる式を教えてください。
特に、E3 の 56 は、商品A、B、D が何個ずつの場合ですか？

No.2 回答者： mike_g 回答日時： 2020/01/05 17:56

確認させてください。

=SUMPRODUCT関数が所謂“積算の和”であることは承知しています。
さて、タイトルに書かれた「除算の和」の意味ですが、
呈示された表の何処を何処で除算(割り算)した和のことを仰っているのですか？

No.1 回答者： 銀鱗 回答日時： 2020/01/05 14:57

SUMIF関数でOK。

　＝SUMIF(検索範囲 , 検索条件 , 合計範囲)
のように記述します。
”検索範囲” と ”合計範囲” が同じセルの場合は ”合計範囲” を省略できます。
　＝SUMIF(検索範囲 , 検索条件)

条件は基本的に「文字列」で記述します。【←これ重要】
　＝SUMIF(B1:B10 , "〇" , A1:A10)
　＝SUMIF(A1:A10 , "2000")
のように指定します。
数値を指定する場合は数値のままでもOK。
　＝SUMIF(B1:B10 , 1 , A1:A10)
　＝SUMIF(A1:A10 , 2000)

ただし、2000以上のような条件を指定する場合は
　＝SUMIF(B1:B10 , ">0" , A1:A10)
　＝SUMIF(A1:A10 , ">=2000")
のように「等号」「不等号」を含めた「文字列」にする必要があります。

・・・本題・・・

質問のケースではエラーのセルを合計から除外したいということですので、
「エラーでないセルを合計の対象とする」という条件を与えてやれば良い。

この条件に
　"<>#N/A"
これを指定すれば良い。

これは
　「#N/Aのエラーではないセル」
という意味になります。
（”＝”は「等しい」、”＜”は「小さい」、”＞”は「大きい」、”＜＞”は「等しくない」を示します。）
他のエラーが含まれる場合は、SUMIFS関数で条件を追加しましょう。
ゼロで割って ”#DIV/0!" というエラーが出た場合はこれを追加ってことです。


・・・余談・・・

ピンポイントでエラーを指定しないと、予期せぬエラーが発生した時に何が悪さをしているのか分からなくなります。
IFERRORなどのエラー処理関数を混ぜないことを勧めます。