Can this be done in a Formula

[PM1]

I have the following Spreadsheet which holds over 4000 lines of data.

I need to go to the first occurence of "9" in Col h (h1) then calculate the sum of Col g until the next re-occurence of "9" in Col h. Therefore E1 would be SUM(g1:g8) = 2055.

Purch Orders BP²I.xls
	F	G	H	I
2	6	1,266.00	9	2055.00
3	6	157.00	8
4	6	62.00	8
5	12	174.00	8
6	6	133.00	8
7	6	169.00	8
8	6	44.00	8
9	6	35.00	8
10	1	15.00	8
11	1	6,365.00	9
12	2	2,641.00	8
13	2	159.00	8
14	2	230.00	8
15	1	44.00	8
16	3	235.00	8
17	1	331.00	8
18	1	35.00	8
19	1	15.00	8
Assets

Edited by Von Pookie

 

[bosco_yip]

or……

{=SUM(G1:INDEX(G:G,MATCH(2,1/(H1:H5000=9),H1:H5000)-1))}

 

[scifibum]

I assume you only want this sum on rows that have "9" in column H, right? (If not - it's unclear what you would want in the other rows.)

If this is what you want then this formula would work (entered in I1 and then copied down)

=IF(H1=9,SUM(OFFSET(H1,0,-1,MATCH(9,H2:H$5000,0))),"")

 

[PM1]

Correct "scifibum" yes I only want the total in Col I where a "9" is found in Col H.

The result from the formula is blank, can this be because it is not summing up the values inCol G?


bosco_yip - yours produces a #DIV/0!

 

[Aladin Akyurek]

Following your exhibit...

=SUM(OFFSET($G$2,MATCH(9,$H$2:$H$65536,0)-1,0,MATCH(9,OFFSET($H$2,J2,,1):$H$65536,0)))

 

[PM1]

Ok - Still getting errors - got to be something silly:

Purch Orders BP²I.xls
	G	H	I	J
1	UnitCost(GBP)	Expense/Asset	AssetValue	GLCode
2	1,266.00	9	#N/A	241370
3	157.00	8	#N/A	241370
4	62.00	8	#N/A	241370
5	174.00	8	#N/A	241370
6	133.00	8	#N/A	241370
7	169.00	8	#N/A	241370
8	44.00	8		241370
9	35.00	8		241370
10	15.00	8		241370
11	6,365.00	9		241370
Assets

 

[PM1]

Aladin,

Thanks for this but it doesn't sum the range G2:G10 before resetting by matching a "9" in column "H".

 

[Aladin Akyurek]

Aladin,

Thanks for this but it doesn't sum the range G2:G10 before resetting by matching a "9" in column "H".

I'm not following you. Try to repost your exhibit along with the manulally-calculated desired results in order to make clear your objective.

 

[PM1]

OK - See if this explains it. I have this worksheet which I use to create an Asset Register:

Purch Orders BP²I.xls
	D	E	F	G	H	I
1	PartNo	Details	Qty	UnitCost(GBP)	Expense/Asset	AssetValue
2	292889-421	ProLiantDL360(G3)R02XeonDP2.8GHz,512MB	6	1,266.00	9
3	292892-B21	XeonDP2800/512-533ProcessorOptionKit	6	157.00	8
4	300678-B21	512MB(266MHz)DDRPC2100RegisteredECCSDRAM	6	62.00	8
5	286714-B22	72.8GB(10000rpm)PluggableHardDiskDrive	12	174.00	8
6	293703-B21	DL380G3HotPlugRedundantPowerSupply	6	133.00	8
7	263825-B21	IntegratedLights-OutAdvancePack	6	169.00	8
8	331903-B21	SlimlineDVD-ROM/CD-RWComboOptionKit	6	44.00	8
9	BUILD	inhousebuildandtest	6	35.00	8
10	DEL-NEXT	delivery	1	15.00	8
11	325134-421	ProLiantDL580(G2)R02Xeon	1	6,365.00	9
12	325254-B21	HPXeonMP2.8GHzProcessrKit	2	2,641.00	8
13	286775-B22	18.2GB(15000rpm)Pluggable	2	159.00	8
14	286776-B22	36.4GB(15000rpm)Pluggable	2	230.00	8
15	331903-B21	SlimlineDVD-ROM/CD-RW	1	44.00	8
16	202171-B21	2GB200MHzDDRPC1600	3	235.00	8
17	203320-B21	DL580(G2)HotPlugMemory	1	331.00	8
18	BUILD	inhousebuildandtest	1	35.00	8
19	DEL-NEXT	delivery	1	15.00	8
Assets

In order to obtain the true value of an Asset I have to identify the "parent + children", namely "9" in Col "H" and "8" below until then next occurence of "9". So the first occurence of "9" is = value of (1266) plus the range G3:G10. From this I am hoping in I2 to show that sum which should = 2055, and in I11 show the sum 10055.

 

[Von Pookie]

Continued here:
http://www.mrexcel.com/board2/viewtopic.php?t=177011