Forum Discussion

beau79au's avatar
beau79au
Copper Contributor
Mar 02, 2023

Sum rows until new number

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?

 

Formulas and Functions
  • FikturFox's avatar
    FikturFox
    Brass Contributor
    Mar 04, 2023

    beau79au 

    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)

Resources