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?
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)))*
(OFFSET(m,,4,IFERROR(MATCH(TRUE,OFFSET(m,1,,15)<>"",0),15)))),0))),
qPrice+tPrice)