Sum rows until new number

Occasional Visitor

Hi everyone,

I have a BOM similar to the attached image.  I then have another sheet for costings with a row for each Main DRG No and the total cost next to it.  Currently it is a manual process of adding the cost to the cost sheet.  Can someone help me with a formula to sum the values under the Main DRG No until it gets to the next Main DRG No?



1 Reply


I'm not sure if I got your query right..

=LET(rMain, B15:B30, rQty, E15:E30, rPrice, F15:F30,
qPrice, MAP(rQty,rPrice,LAMBDA(q,p, IF(q="",0,q*p))),
tPrice, MAP(rMain,LAMBDA(m, IF(m<>"",SUMPRODUCT((OFFSET(m,,3,IFERROR(MATCH(TRUE,OFFSET(m,1,,15)<>"",0),15)))*