I want to sumproduct QUANTITY and WEIGHT to output TOTAL WEIGHT for all items up until the last blank cell of each PACKAGE. The TOTAL WEIGHT output needs to be on the same row as its corresponding package number.

In the example spreadsheet below, package 1 (highlighted in yellow) has 4 rows of item but the package number will only be entered on the first row, meaning the package number will not always be entered on the same row depending on how many items there are in each package.

https://docs.google.com/spreadsheets/d/1csWgASMigadwqzU0U7wedetUFdPuIJ5pbLcNzveb3qo/edit?usp=sharing

Thanks in advance!

CodePudding user response：

=ArrayFormula(IF(A2:A="",,VLOOKUP(A2:A,QUERY(QUERY({IF(B2:B="",,LOOKUP(ROW(A2:A),IF(A2:A="",,ROW(A2:A)),A2:A)),A2:D},"select Col1, Col4*Col5"),"select Col1, sum(Col2) group by Col1"),2,FALSE)))