Retrieve Sum value for dynamic data

[Brad B]

Hi,

I'm trying to create a formula that will allow me use some exported data to create a simple summary of it, which will be used for other calculations.

Our system exports data that looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item1[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]I want Item1 SUM here[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lot1[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD]I want to drag the same formula all the way down, not modify a new one for each item, because the items will not be in the same place every time[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lot2[/TD]
[TD][/TD]
[TD]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sum:[/TD]
[TD]1500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I want Item2 SUM here[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lot1[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lot2[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lot3[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sum:[/TD]
[TD]900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
And so on...

I need to reference the item (I plan to use vlookup to get the sum for each item later, and I'm trying to create an intermediate step to get the sum for each item in the same row...maybe there is an easier way?)

Anyway, ultimately what I need is data that looks like this so I can use it elsewhere in the workbook.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Item1[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item2[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I started with something like =IF($B2<>"",OFFSET($B2,COUNTA(C2:C4)+1,3),"")

I think I can use offset or index somehow, but I'm not sure how to because when I use COUNTA to find the amount of rows under an item, I don't know how to make sure I don't run into the data set for the next item (or make sure I capture all the data in a block), because the data sets can be of a different size every time (Not just Item1 being 2 rows and item 2 being 3 rows, but each item may vary daily: Item1 may have 3 lot numbers (rows) one day and 7 rows the next).

I'm sure this is doable, but I'm not sure how. Thanks for your help!

 

[StephenCrump]

Perhaps:

F2: =IF(LEFT(B2,4)="Item", SUMPRODUCT(--(LEFT(C2:C$15,3)="Lot"),E2:E$15)-SUM(F3:F$15),"")

Excel 2010
	A	B	C	D	E	F
1	Date
2		Item1				1,500
3			Lot1		1,000
4			Lot2		500
5				Sum:	1,500
6		Item2	Lot1		150	285
7			Lot2		25
8			Lot3		40
9			Lot 4		70
10				Sum:	285
11		Item3				900
12			Lot1		300
13			Lot2		300
14			Lot3		300
15				Sum:	900
Sheet1

 

[ZVI]

Depending on data layout, it also could be something like this:

Book1
	A	B	C	D	E	F	G
1	Date
2		Item1				1500	Copy F2 down
3			Lot1		1000
4			Lot2		500
5				Sum:	1500
6		Item2				900
7			Lot1		300
8			Lot2		300
9			Lot3		300
10				Sum:	900
Sheet1

Cell Formulas
Range	Formula
F2	=IF(B2="","",VLOOKUP("Sum:",D2:$E$10000,2,0))

Or like that:

Book1
	A	B	C	D	E	F
1	Date
2		Item1			1500	Copy E2 down
3			Lot1	1000
4			Lot2	500
5		Item2			900
6			Lot1	300
7			Lot2	300
8			Lot3	300
Sheet2

Cell Formulas
Range	Formula
E2	=IF(B2="","",SUM(D2:INDEX(D:D,ROW()+SUMPRODUCT(MATCH(1=1,D3:$D$10000="",0),1))))