APPROACH FROM A DIFFERENT WAY

PM1

Board Regular
Joined
Oct 28, 2005
Messages
192
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi,

Try:

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

in I2 and drag down.
 
Upvote 0
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
 
Upvote 0
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 :rolleyes:
 
Upvote 0
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...
 
Upvote 0
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 :cry:
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
 
Upvote 0
=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...
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top