エクセルで複数のセルの合計をIF関数も含めて出す方法

質問

初歩的だと思うのですが・・
異なった文字が入った各セルの合計を別セルにてIFを使用し、数字を出しながらなおかつ集計する方法がわかりません。

　A　　　B　　　 C　　　D

1 い　　　ろ
2 　　　　は　　　に　　
3 ほ　　　　　　
4 へ　　　と
5

A列に文字が入ると常に　500
B列に文字が入ると常に　200
C列に文字が入ると常に　100

として、D列にA-Cの合計（何もなければ空欄）を出したいのです。この場合だとD1=700, D2=300, D3=500...となるように。
IF(A1="","",500)+IF(B1="","",200)+IF(C1="","",100)
のちゃんとした関数式を教えてください。よろしくお願いします。。

回答No.2

noname#9284 · ベストアンサー

A1:C1を選択して右クリックし、挿入－下方向にシフト－ＯＫしてください。

新しく出来たA1:C1に　５００　２００　１００　と入力して置いてください。
文字色を白にして隠す方法もあります。

D2に　=SUMPRODUCT((A2:C2<>"")*$A$1:$C$1)　と入力し、
これを下までコピーしてください。
もしその1行になにも入力されていなければ０が戻ります。
何も入力されていない時に空白を表示したいのであれば
D2は　=IF(COUNTA(A2:C2)=0,"",SUMPRODUCT((A2:C2<>"")*$A$1:$C$1))　がいいでしょう。

COUNTBLANK関数は、空白ではないセルの数を数えます。
IF(COUNTA(A2:C2)=0,""　は、
空白ではないセルが０個なら””表示をしなさい、という意味です。

また、1行挿入することが出来なければ
D1に　=IF(COUNTA(A1:C1)=0,"",SUMPRODUCT((A1:C1<>"")*{500,200,100}))
という数式を入れて下にコピーしてください。

お礼

早速のお答えありがとうございます！！

補足

COUNTもSUMPRODUCTも初体験です（恥）。感動です。
ただ、同じように入力してみたのですが、何もないときに空白にならずに０になってしまいます。。

回答No.7

mshr1962

応用偏になりますがこんな方法もあります。
=SUMPRODUCT(ISTEXT(A1:C1)*1,{500,200,100})
SUMPRODUCT関数は対応する範囲の積の和を求めます。
ISTEXT(A1:C1)*1 でA～Cに文字があると1,ない場合は0になります。
数字にも対応の場合は、
=SUMPRODUCT((A1:C1<>"")*1,{500,200,100})
にしてください。

上記例の1行目の場合
{1,1,0}*{500,200,100}=500+200+0=700 になります。

3列とも空欄で非表示で文字だけの場合は
=IF((COUNTBLANK(A1:C1)+COUNT(A1:C1))=0,"",SUMPRODUCT(ISTEXT(A1:C1)*1,{500,200,100}))
数字にも対応なら
=IF(COUNTBLANK(A1:C1)=0,"",SUMPRODUCT((A1:C1<>"")*1,{500,200,100}))

お礼

わかりやすい説明ありがとうございます！今回はポイントさしあげられなくて本当にすみません。。

回答No.6

noname#9284

D1の数式を
=IF(COUNTIF(A1:C1,"")=3,"",SUMPRODUCT((A1:C1<>"")*{500,200,100}))
のように
IF(COUNTIF(A1:C1,"")=3,""
””が３つなら””
と数えさせてみてはいかがでしょうか。
時間がありませんので取り急ぎ書かせていただきました。

お礼

お忙しいところまたもや即答ありがとうございます。完璧です！！応用して使いこなせるまで先は長いですが・・。本当に助かりました。これからもまたよろしくお願いします。。

回答No.5

noname#9284

補足を拝見しました。

> ただ、同じように入力してみたのですが、何もないときに空白にならずに０になってしまいます。。

セル範囲の中に、空白に見えて実はスペースが入力されているセルはないでしょうか？
スペースはれっきとした文字列なので
COUNTA関数によってカウントされてしまいます。
範囲をドラッグしてdeleteを押してみてもう一度数式の戻り値を確かめてくださいませんか？

補足

何度も即答ありがとうございます。
今確認したところ、どうやらセル内（この場合A～C）にまた別の数式が入っているからみたいです。実際作成しているシートだとこの例えのA～CがG～Iの範囲にあり、Aからずっと連携して数式が入っている状態なのです。。

回答No.4

imogasi

D1セルに
=COUNTA(A1)*500+COUNTA(B1)*200+COUNTA(C1)*100
をいれて、ｄ２以下に複写する。
数字が入っていてもカウントしてしまいますが、その懸念がなければこれで使えますでしょうか。
数字セルを除いてカウントするなら
=(COUNTA(A1)-COUNT(A1))*500+(COUNTA(B1)-COUNT(B1))*200+(COUNTA(C1)-COUNT(C1))*100
でどうでしょう。後者はテスト不十分ですが、ざっとやって見ると正しいようですが。

お礼

早速のお答えありがとうございます！！

補足

そのまま入力してみたのですが、文字も数字も入っていなくてもすべて合計してしまいました。（この場合だと８００）えーん。

回答No.3

noname#9284

訂正があります。
「COUNTBLANK関数は、空白ではないセルの数を数えます。」
　　　　　　　　　　　　↓
「COUNTA関数は、空白ではないセルの数を数えます。」

でした。驚かしてすみません。

回答No.1

4500rpm

力業で
=IF(IF(A1="",0,500)+IF(B1="",0,200)+IF(C1="",0,100)=0,"",(IF(A1="",0,500)+IF(B1="",0,200)+IF(C1="",0,100)))
スマートなやり方は他の人の回答を見ましょう。

お礼

早速のお答えありがとうございます！　助かりました。
まだまだ関数初心者の私にはこれが一番シンプルです（笑）。