Can this be done in a Formula

Status
Not open for further replies.

PM1

Board Regular
Joined
Oct 28, 2005
Messages
192
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
FGHI
261,266.0092055.00
36157.008
4662.008
512174.008
66133.008
76169.008
8644.008
9635.008
10115.008
1116,365.009
1222,641.008
132159.008
142230.008
15144.008
163235.008
171331.008
18135.008
19115.008
Assets


Edited by Von Pookie
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
or……

{=SUM(G1:INDEX(G:G,MATCH(2,1/(H1:H5000=9),H1:H5000)-1))}
 
Upvote 0
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))),"")
 
Upvote 0
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!
 
Upvote 0
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)))
 
Upvote 0
Ok - Still getting errors - got to be something silly:
Purch Orders BP²I.xls
GHIJ
1UnitCost(GBP)Expense/AssetAssetValueGLCode
21,266.009#N/A241370
3157.008#N/A241370
462.008#N/A241370
5174.008#N/A241370
6133.008#N/A241370
7169.008#N/A241370
844.008241370
935.008241370
1015.008241370
116,365.009241370
Assets
 
Upvote 0
Aladin,

Thanks for this but it doesn't sum the range G2:G10 before resetting by matching a "9" in column "H".
 
Upvote 0
PM1 said:
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.
 
Upvote 0
OK - See if this explains it. I have this worksheet which I use to create an Asset Register:
Purch Orders BP²I.xls
DEFGHI
1PartNoDetailsQtyUnitCost(GBP)Expense/AssetAssetValue
2292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB61,266.009
3292892-B21XeonDP2800/512-533ProcessorOptionKit6157.008
4300678-B21512MB(266MHz)DDRPC2100RegisteredECCSDRAM662.008
5286714-B2272.8GB(10000rpm)PluggableHardDiskDrive12174.008
6293703-B21DL380G3HotPlugRedundantPowerSupply6133.008
7263825-B21IntegratedLights-OutAdvancePack6169.008
8331903-B21SlimlineDVD-ROM/CD-RWComboOptionKit644.008
9BUILDinhousebuildandtest635.008
10DEL-NEXTdelivery115.008
11325134-421ProLiantDL580(G2)R02Xeon16,365.009
12325254-B21HPXeonMP2.8GHzProcessrKit22,641.008
13286775-B2218.2GB(15000rpm)Pluggable2159.008
14286776-B2236.4GB(15000rpm)Pluggable2230.008
15331903-B21SlimlineDVD-ROM/CD-RW144.008
16202171-B212GB200MHzDDRPC16003235.008
17203320-B21DL580(G2)HotPlugMemory1331.008
18BUILDinhousebuildandtest135.008
19DEL-NEXTdelivery115.008
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.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,211,984
Messages
6,105,219
Members
447,957
Latest member
Basildon

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