Formula to subtotal previous column that dynamically changes so need to find blanks to know which rows to subtotal

[bjurick]

What is the formula that I can use in Column C? I don't want to have to change any cell references as this data changes all the time so it will have to be smart enough to detect blank cells in column B and then place the subtotal in column C. Also, the subtotal in column C is only for a "dynamic" range in between the blank cells in column B. Any help would be greatly appreciated. [TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 1
[/TD]
[TD]200.00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 2
[/TD]
[TD]300.00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD][/TD]
[TD]500.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 3
[/TD]
[TD]100.00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 4
[/TD]
[TD]500.00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 5
[/TD]
[TD]400.00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD][/TD]
[TD]1000.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 6
[/TD]
[TD]100.00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 7
[/TD]
[TD]150.00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 8
[/TD]
[TD]250.00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 9
[/TD]
[TD]300.00
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD][/TD]
[TD]800.00
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaFrog]

<br />

Book1
	A	B	C
1	Item 1	200
2	Item 2	300
3	Total		500
4
5	Item 3	100
6	Item 4	500
7	Item 5	400
8	Total		1000
9
10	Item 6	100
11	Item 7	150
12	Item 8	250
13	Item 9	300
14	Total		800
15
16
Sheet1

Cell Formulas
Range	Formula
C2	=IF(AND(B1<>"",B2=""),SUM($B$1:B2)-SUM($C$1:C1),"")

Copy the C2 formula down column C

 

[bjurick]

	A	B	C
Item 1
Item 2
Total
Item 3
Item 4
Item 5
Total
Item 6
Item 7
Item 8
Item 9
Total

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]300[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]500[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]400[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]150[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]250[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]300[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]800[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Worksheet Formulas[TABLE="width: 100%"] <thead>[TR="bgcolor: #E0E0F0"] [TH="width: 10"]Cell[/TH] [TH="align: left"]Formula[/TH]

<tbody> [TD]
</thead><tbody>
[TH="width: 10, bgcolor: #E0E0F0"]C2[/TH]
[TD="align: left"]=IF( AND(B1<>"",B2=""),SUM($B$1:B2)-SUM($C$1:C1),"" )[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]



Copy the C2 formula down column C

Excellent. Thank you so much. Works great!!

Thanks