What I'm trying to accomplish is going to be illustrated from the below picture, the example spreadsheet is linked as well.

I need to create function that will sum Quantity Columns G & D where ID Columns B & E is less than 5000. Then display those results to the corresponding ID in Column J. Lastly, the quantities for the Materials in the Chain Material section need to carry over to the respective ID in Column H.

I do NOT need to sum anything from Column D where the ID from Column B is greater than 5000, that information is useless.

Expected result can be seen next to each cell in Column K.

Thank you in advance!

CodePudding user response：

use in J4:

=INDEX(IFNA(VLOOKUP(H4:H, QUERY({B4:D; E4:G}, 
 "select Col1,sum(Col3) group by Col1"), 2, 0)))