（エクセル）数値だけ合計したい

質問

複数のセルに入力されている数値だけを抽出し、合計したいと思います。

　　Ａ　　　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　　Ｆ　　　　Ｇ
1　合計金額　金額1　　金額1理由　金額2　　金額2理由　金額3　　金額3理由
2　　　　　　1000円　　　菓子　
3　1500円 　1000円　　　菓子　　 500円　 　飲料
4　1520円 　1000円　　　菓子　　 500円　 　飲料　　　20円　 　消費税

以上のようにＡ列に合計金額を計算させる数式を考えています。
（ルール）
・Ｂ列とＣ列、Ｄ列とＥ列、Ｆ列とＧ列はセットです。例えば、Ｂ列には入力されているが、Ｃ列は空白ということはない
・Ｂ列とＣ列→Ｄ列とＥ列→Ｆ列とＧ列の順に入力されていく
・2行目、3行目のように、常に金額が3つとも入力されているとは限らない
・金額が入力されているＢ列、Ｄ列、Ｆ列には、「○○円」と「円」も一緒に入力されている

（やりたいこと）
Ａ列に合計金額を反映させないのですが、以下のように規制したいです
（１）2行目のように、金額が1つだけの場合（Ｂ列とＣ列しか入力されていない場合）は、合計金額は空白セルにする
（２）3行目、4行目のように、金額が2つ以上の場合（Ｄ列とＥ列や、Ｆ列とＧ列にも入力されている場合）は、合計金額を反映させる
（３）合計金額は「○○円」と「円」も一緒に反映させる

（考えたこと）
Ｂ列の金額とＢ列+Ｄ列+Ｆ列の合計を比較し、同じならＡ列は空白、違う場合はＢ列+Ｄ列+Ｆ列の合計をＡ列に反映させるようＩＦ関数を使えばいいのではないか、と考えました。
そこで、まずＢ列+Ｄ列+Ｆ列の合計を出す関数を作ってみたのですが、うまくいきませんでした。

=SUM(SUBSTITUTE(B2,"円","")*1,SUBSTITUTE(D2,"円","")*1,SUBSTITUTE(F2,"円","")*1)
とすると、金額が3つとも入力されていればいいですが、一つでも空白セルがあると、#VALUE！とエラーになってしまいます。
SUMPRODUCTを使用してできるかと思いましたが、同じ結果でした。

どうすれば、思っているようになるのでしょうか。
ご存知の方がいましたら教えてください。
よろしくお願いします。

回答No.3

mt2008 · ベストアンサー

本当に項目が3つだけならこんな感じでも
A2セルに
=IF(D2="","",SUM(SUBSTITUTE("0"&B2,"円",""),SUBSTITUTE("0"&D2,"円",""),SUBSTITUTE("0"&F2,"円","")))

空白対策に金額の頭に「0」を付けるのがポイント

お礼

回答ありがごうございます。
教えていただいた方法で行うことにしました。
範囲がトビトビなので、一つずつSUBSTITUTEをやらなけばいけないのは、しょうがないですね。
ありがとうございました、

回答No.4

dogs_cats

他の方も書かれてる通り　ユーザー定義書式で数値入力に変更するのが最も簡単な方法です。

どうしてもこのままでというのでしたら、下記方法でも可能です。
=IFERROR(LEFT(B2,FIND("円",B2)-1)+0,0)+IFERROR(LEFT(D2,FIND("円",D2)-1)+0,0)+IFERROR(LEFT(F2,FIND("円",F2)-1)+0,0)

お礼

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

いろいろためしたのですが、このままでいかないと駄目なようです。
ありがとうございました。

回答No.2

asciiz

本来数値を扱うべきセルに、「1000円」という文字列を入れてしまっているのが問題だと思います。

金額の欄は、すべて「1000」「500」など、数値だけ、入れましょう。
そして、セルの書式設定から、ユーザー定義で、「0"円"」と指定すれば、見た目は「1000円」などになります。

そうしておくと、A列は例えば
[A2] =sum(B2:Z2)

というような、非常に単純な式で済みます。(sum関数で文字列はカウントされませんので)
※A列の表示書式も同じく、「0"円"」にしておきます。
金額っぽく、3桁区切りのカンマが欲しければ、「#,##0"円"」という書式指定なんかもできます。

お礼

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

ＮＯ１の方と同じなのですが、実は、その後にワードの差し込み印刷する流れだったのですが、”円”は反映されませんでした。
なので別の方法でトライしたいと思います。
ありがとうございました。

回答No.1

shintaro-2

まず、
円については、ユーザー書式表示（#"円"）で表示させることとして
円という文字をデータから消してください。

そうすれば簡単です。

お礼

教えていただきありがごうございます。
確かにエクセルでは、うまくいきます。
実は、その後にワードで差し込み印刷する流れだったのですが、”円”は反映されないんですね。
なので別の方法でトライしたいと思います。
ありがとうございました。