空白セルまでの合計を求める方法

質問

いつもお世話になってます。
下記のような売上のリストを作成しています。

A　　　　　B　　　 　　　C　　　　　D　　　　E　　　　F　　　　　　　　　　G
日付　　受付No.　　品物　　　数量　　 単価　　数量×単価　　合計金額
7/4　　　11-1　　　　　りんご　　　 1　　　100　　　100　　　　　　　　　700
　　　　　　　　　　　　　ばなな　　 　3　　　150　　　450
　　　　　　　　　　　　　キウイ　　 　1　　　150　　　150
7/5　　　11-2　　　　　りんご　　　 1　　　100　　　100　　　　　　　　　500
　　　　　　　　　　　　　みかん　　　2　　　200　　　400
7/5　　　11-3　　　　　ばなな　 　 3　　　150　　　450 　　　　　　　　450

受付番号ごとに合計金額を集計します。
受付番号ごとに品物の種類は1種類だったり2種類,、ないしは3種類だったり等…異なります。
今は合計金額を直接入力していますが、数式を入れて簡略化したいと考えてます。

SUMとOFFSETと組み合わせてやろうとしましたが、品物が2種以上の場合は受付No.に空白がありうまくいきません。
どのようにすればスムーズに合計金額が出せるのかご教示いただければと思います。

回答No.5

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

G2セルに
=IF(B2="","",SUM(F2:F$600)-SUM(G3:G$600))
下へオートフィル
最下行は適宜変更してください

#掲示板の回答でOFFSET関数を必要としたことが過去に1度あっただけの気がする。。。

回答No.4

kagakusuki

今仮に、リストの最下段の行の行番号が999以下であるものとします。
　その場合、G2セルに次の数式を入力してから、G2セルをコピーして、G3以下に貼り付けると良いと思います。

=IF($B2="","",SUM(OFFSET($F2,,,IF(COUNT($A$1:$A2)=COUNT($A:$A),999,MATCH("*",$B3:$B$999,0)))))

回答No.3

tom04

こんばんは！
ご希望の方法とは違うかもしれませんが・・・
VBAでの一例です。

画面左下にある操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Columns(1).Insert
Dim i As Long
For i = 2 To Cells(Rows.Count, 4).End(xlUp).Row
If Cells(i, 2) <> "" Then
Cells(i, 1) = Cells(i, 3)
Else
Cells(i, 1) = Cells(i - 1, 1)
End If
Next i
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(i, 3) <> "" Then
Cells(i, 8) = WorksheetFunction.SumIf(Columns(1), Cells(i, 3), Columns(7))
End If
Next i
Columns(1).Delete
End Sub 'この行まで

以上、参考になれば良いのですが
最初に書いたように関数での方法がご希望なら読み流してくださいね。m(_ _)m

回答No.2

keithin

簡単な方法：
Ｂ列に「文字列」で受付No.を記入してあるとして
最大１０行まで空白が続く可能性があるとして
Ｇ２：
=IF(B2="","",SUM(OFFSET(F2,0,0,IF(COUNTA(B3:B12)=0,1,MATCH("*",B3:B12,0)),1)))

＃Ｂ列に「文字列」が来るか「数値」が入るか決められない場合
Ｇ２に
=IF(B7="","",SUM(F7:INDEX(F:F,MIN(IF(B8:B17<>"",ROW(B8:B17)-1,9999)))))
と記入し，コントロールキーとシフトキーを押しながらEnter（←忘れず行う）して入力します。

回答No.1

noname#204879

》 SUMとOFFSETと組み合わせてやろうとしましたが、
》 品物が2種以上の場合は受付No.に空白がありうま
》 くいきません

OFFSETをご存知とは、貴方は Excel の達人かも。
「空白」がネックになっているなら、それを埋めるのが得策かと。

１．列Ａ、Ｂ全体を選択
２．[編集]→[ジャンプ]→[セル選択]を実行
３．“空白セル”に目玉入れ
４．[OK]をツン
５．式　=A2 を入力して、Ctrlキーを抑えたままで Enterキーを「エイヤッ！」と叩き付け

以上の操作で、空白セルが一瞬にして埋まります。(添付図参照)

G2: =IF(B2=B1,"",SUMIF(B$2:B$15,B2,F$2:F$15))