続 エクセル イレギュラーなSUM関数

質問

先程こちらでhttp://oshiete1.goo.ne.jp/qa5428194.html
イレギュラーなSUM関数を質問しお答えいただきましたが
新たに問題が出ましたのでもう一度質問いたします

*さきほどの質問の内容はこちらです*

A列B列があり
A5にはA１～A４の合計のSUM関数が入っています。

B5にもSUM関数を入れたいのですが

B１～B４はイレギュラーな事があった場合のみ数字を入力し
B５にはB１～B４に入力された数字の合計と
B列に何も入力されていない隣のA列の合計を表示させたいのです。

例えるなら

A1～A４に３を入力
A5はSUM関数がはいっているので１２が表示されているとして

B列はB１～B３は未入力
B４に７が入力された場合
B５にはA1～A３とB４の合計の１６が表示されるようにしたいのです。

B４に７を入力したので隣のA４の３は合計しないという事です。

*回答はこちらです*

回答１
配列関数で
=SUM(IF(B1:B4="",A1:A4,B1:B4))
と入力して、Ctrl+Shift+Enterで決定したら、式が{}でくくられて配列関数になります。
式の意味は　B1～B4が空白の場合は、A1～A4の値を、それ以外はB1～B4の値を出して合計

回答２
=SUMPRODUCT(NOT(B1:B4)*A1:A4+B1:B4)
あたりですかね。not関数のかわりにisblank関数の方が判りやすい
かもしれないけど。

TRUE/FALSEの論理値は、四則演算にぶち込むと1/0の数値として扱
われます。また、空は四則演算にぶち込むと0の数値として扱われ
ます。だから、「B列が空である」がFALSEならA列とのかけ算はゼ
ロになりB列の値が加算され、TRUEならA列の値に1をかけて0を足し
たことになります。後はそれを1行目から4行目にわたって配列とし
て計算して合計してくれるsumproduct関数に放り込むだけ。

問題はこの方法だと
B列に何も入力しない場合、A列の合計がB５に出て来てしまいます。
B列に何も入力しない場合、B５にも何も入力しないようにしたいのです。

ちなみに先程は記述しませんでしたが
B列には自動で数字に〔〕が付くようにユーザー定義で指示していて
B５はB列が未入力で合計が０でも〔0〕と表示されないように
ユーザー定義で　"〔"#"〕";;;が入っていてます。

やりたい事をまとめると
●B列には自動で数字に〔〕を付ける
●B１～B４に数字が入力された場合はB5に
　B１～B４の合計とB列に何も入力されていない隣のA列の合計を表示させたい
　がB列が未入力ならB5に何も表示しない

ややこしくて申し訳ありません。
そして確認不足で申し訳ありませんでした。

どうぞ宜しくお願い致します。

回答No.3

maron--5 · ベストアンサー

◆こんな方法はいかがでしょう
A5=SUM(A1:A4)

B5=IF(COUNT(B1:B4),SUMIF(B1:B4,"",$A$1:$A$4)+SUM(B1:B4),"")
★B5の式はEnterで式を確定して、下図の例では右にコピー

★B列に「0」を入力されることも考慮して、B1:B5の表示形式は
★ユーザー定義で、"["0"]"　とします

お礼

シンプルですごくいいです！
その上、０を入力する事も考慮いただきまして
ありがとうございます！０を入力する事もあるのを気付いていませんでした！

回答No.4

某HN クロメート(Chromate)（@CoalTar）

＃２です
<<追加>>
B列に数値しか入らないなら
=SUM(A5,B1:B4,-SUMIF(B1:B4,"<>",A1:A4))
逆もまた然り
=SUM(SUMIF(B1:B4,"",A1:A4),B1:B4)

＞●B列には自動で数字に〔〕を付ける
＞B列には自動で数字に〔〕が付くようにユーザー定義で指示していて
でＯＫ。 でも正の値しか入力しないのかな

お礼

正の値のみ入力します

こちらではBが未入力の時にはB５にはAの合計が出て来てしましたした
でも気に掛けてくれて２度も回答くださってありがとうございました！

回答No.2

某HN クロメート(Chromate)（@CoalTar）

足したものを引けばよいような？
=IF(COUNT(B1:B4)=0,"",
SUM(A5,B1:B4,-SUMIF(B1:B4,">="&MIN(B1:B4),A1:A4)))

お礼

シンプルですごくいです！
ありがとうございました！

回答No.1

hallo-2007

ついでに
B列に入力されている文字数を配列関数で出して見ます。
B6セルに
=SUM(LEN(B1:B4))　Ctrl+Shift+Enter
意味は、B1～B4の文字の長さを出して合計します。
全てが未入力であれば　０ですね。
IF文と組み合わせれば、
=IF(SUM(LEN(B1:B4))=0,"",SUM(IF(B1:B4="",A1:A4,B1:B4)))
こんな感じでしょうか。
これを機会に　エクセル　配列関数について勉強してみると良いです。
Webで検索してみてください。

お礼

早速のご回答ありがとうございました。
こちらの方法で出来たのですがB６のセルに
数字が表示されてしまうのはちょっと…
表示させない方法もあるとは思いますがシンプルにしたかったので
ポイントナシでスミマセン