# APPROACH FROM A DIFFERENT WAY

#### PM1

##### Board Regular
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
GHIJ
1UnitCost(GBP)Expense/AssetAssetValueGLCode
21,266.0092,055241370
3157.008241370
462.008241370
5174.008241370
6133.008241370
7169.008241370
844.008241370
935.008241370
1015.008241370
116,365.00910,055241370
122,641.008241370
13159.008241370
14230.008241370
1544.008241370
16235.008241370
17331.008241370
1835.008241370
1915.008241370
Assets

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### fairwinds

##### MrExcel MVP
Hi,

Try:

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

in I2 and drag down.

#### PM1

##### Board Regular
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
FGHI
1QtyUnitCost(GBP)Expense/AssetAssetValue
261,266.0092,055
36157.008
4662.008
512174.008
66133.008
76169.008
8644.008
9635.008
10115.008
1116,365.00910,055
1222,641.008
132159.008
142230.008
15144.008
163235.008
171331.008
18135.008
19115.008
20138,960.6491,993,261
2142,753.25930,457
Assets

#### barry houdini

##### MrExcel MVP
Try using

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

#### PM1

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular

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

YOU LOT ARE GREAT :wink:

#### PM1

##### Board Regular
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
GHIJ
24096,931.46910,465241400
2410242.608 241400
24112,077.708 241400
24121,039.728 241400
2413173.298 241400
2414825.009#N/A241370
2415522.008 241370
2416132.008 241370
2417298.008 241370
2418271.008 241370
2419114.008 241370
2420104.008 241370
242125.008 241370
2422176.008 241370
242335.008 241370
242415.008 241370
Assets

#### fairwinds

##### MrExcel MVP
=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...

Replies
2
Views
186
Replies
2
Views
247
Replies
7
Views
201
Replies
0
Views
406
Replies
4
Views
443

1,195,679
Messages
6,011,119
Members
441,585
Latest member
MargaretBartley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back