APPROACH FROM A DIFFERENT WAY

[PM1]

I previously posted a similar question but I think I failed in getting across my question, however after many sleepness nights I want to see if it can be approached another way.

What I need to do is "lookup" the No 9 in Col "H", when it finds an instance store the value in Col "G", then add to the value stored in "G" all instances of the no 8 in Col "H" until the next 9 is reached, this should then be outputted in Col "I" next to the instance of 9. See extarctions below:

Purch Orders BP²I.xls
	G	H	I	J
1	UnitCost(GBP)	Expense/Asset	AssetValue	GLCode
2	1,266.00	9	2,055	241370
3	157.00	8		241370
4	62.00	8		241370
5	174.00	8		241370
6	133.00	8		241370
7	169.00	8		241370
8	44.00	8		241370
9	35.00	8		241370
10	15.00	8		241370
11	6,365.00	9	10,055	241370
12	2,641.00	8		241370
13	159.00	8		241370
14	230.00	8		241370
15	44.00	8		241370
16	235.00	8		241370
17	331.00	8		241370
18	35.00	8		241370
19	15.00	8		241370
Assets

 

[fairwinds]

Hi,

Try:

=IF(H2=9,SUM(G2:INDEX(G3:$G$100,MATCH(9,H3:$H$100,0)-1)),"")

in I2 and drag down.

 

[PM1]

Fairwinds,

It works a treat up until the point where there are 2 rows each containing a 9, then I get a really weird sum, after that it corrects itself and proceeds fine until the next instance:

Purch Orders BP²I.xls
	F	G	H	I
1	Qty	UnitCost(GBP)	Expense/Asset	AssetValue
2	6	1,266.00	9	2,055
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	10,055
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
20	1	38,960.64	9	1,993,261
21	4	2,753.25	9	30,457
Assets

 

[barry houdini]

Try using

=IF(H2=9,SUM(G2:INDEX(G2:$G$100,MATCH(9,H3:$H$101,0))),"")

 

[PM1]

Barry,

If I take out the -1 within the formula then it adds the value of the next occurence of 9.

I'm trying to get it to work with a If AND() statement to check that there is not 2 occurences of 9, so far I've failed

 

[fairwinds]

Try:

=IF(H2=9,SUM(G2:INDEX(G2:$G$100,MATCH(9,H3:$H$100,0))),"")

Not only take out -1, also change G3 to G2.



EDIT: as Barry did...

 

[PM1]

SUCCESS..............

YOU LOT ARE GREAT :wink:

 

[PM1]

Ooooppppssss: Jumped the gun a little, when it gets to the last occurence of 9 it returns #N/A. I've adjusted the range to the size of the sheet but to no avail

Purch Orders BP²I.xls
	G	H	I	J
2409	6,931.46	9	10,465	241400
2410	242.60	8		241400
2411	2,077.70	8		241400
2412	1,039.72	8		241400
2413	173.29	8		241400
2414	825.00	9	#N/A	241370
2415	522.00	8		241370
2416	132.00	8		241370
2417	298.00	8		241370
2418	271.00	8		241370
2419	114.00	8		241370
2420	104.00	8		241370
2421	25.00	8		241370
2422	176.00	8		241370
2423	35.00	8		241370
2424	15.00	8		241370
Assets

 

[fairwinds]

=IF(H2=9,IF(ISNUMBER(MATCH(9,H3:$H$100,0)),SUM(G2:INDEX(G2:$G$100,MATCH(9,H3:$H$100,0))),SUM(G2:$G$100)),"")

But you could also add a 9 at the end...