Excel2007で文字列を含む複数条件の計算を関数で求めたいのですが

質問

Excel2007で文字列を含む複数条件の計算を関数で求めたいのですが・・・・。

はじめまして。添付画像のようなデータを作成している途中なのですが、自分の思う返り値がでないためご教授いただければと思います。

添付画像のように、日々入力する作業があります。

２行目の数値は　日付が（１～31）入っています。（条件書式で日曜日を色付けしてます）
Ｂ列に【緊急】か【その他】を【☆】で分けています。
表内の数字は　時間数を入力します。
（暗算しやすくするため仮の数字を入れています。）
１日７時間以上の場合は定額3500円
７時間未満の場合は１時間当たり500円
それをＢ列の条件によって計算できるようしています。
（ちなみに１～５日までの金額（１１行目と１４行目）行へはcountifs，sumifs関数を利用して求めています。）

このように数字のみであれば問題ないのですが、７日、８日（Ｊ列，Ｋ列）のように文字列”◎”が入ると思うような答えが導き出せず、日々の計算に適した関数がわからず困っています。

１１行目へＢ列が空白の時、７時間以上は3500円　７時間未満は500円を　“◎”が付いても数値を計算ができ、なおかつ、◎がついたら＋300円　

１４行目へＢ列が☆（空白じゃない）の時、７時間以上は3500円　７時間未満は500円を　“◎”が付いても数値を計算ができ、なおかつ、◎がついたら＋300円　を入れることができる関数の組み合わせは存在するでしょうか？

横計（個人当たりの1ヶ月計）は作業列をつくり、
【〔=SUM(IF(LEN(D3:AH3)>1,IF(SUBSTITUTE(D3:AH3,”◎”,””)*1>6,3500,0)))+SUM(IF(LEN(D3:AH3).1,IF(SUBSTITUTE(D3: AH3,”◎”,””)*1<7,500*SUBSUTITUTE(D3:AH3, ,”◎”,””)*1,0)))〕】を配列数式を使っていれてます。（D3:AH3は１～３１日までの範囲です。）
これを縦計に応用できないかと思って実行したのですが、どうしてもＢ列の条件が入るとできなくて困ってます。

よい方法があれば教えていただければ大変助かります。
どうぞよろしくお願いいたします。

回答No.1

MackyNo1

説明は長いのですが、質問の趣旨がよくわかりません。

B列に未入力のセルとそうではないセルを別々に集計したいということでしょうか？

その場合は、単純にIF関数の部分に以下のような条件を追加するだけでうまくいくはずです。

=IF(AND($B$3:$B$9="",元の条件),・・・）

具体的な数式が作成できないなら、B列の条件がない場合にうまく集計できる数式を提示してください。

補足

ご指摘ありがとうございます。わかりにくい説明で申し訳ありません。。。

入れたい数式の場所は　11行目と14行目で、
11行目は、現在 D11、
＝COUNTIFS($B$3:$B$9,"",D3:D9,">=7")*$B$19 + SUMIFS(D3:D9,$B$3:$B$9,"",D3:D9,"<7")*$A$19
が入っています。（それがAH11までオートフィルでコピーしています。）

D11には3500が値で返っています。

ですが、
Ｊ列のように、文字列（J3の◎５や　J7の◎８など）が入った場合でも、文字列を数値として計算できるようにと思っています。

文字列だけで考えれば、質問文にあげた　SUBTITUTEと配列数式を使用すれば計算できますが、
それにプラスで　B列の空白の欄に該当するものだけを11行目に日々集計をしていく方法を探しております。

どうぞよろしくお願いいたします。