How to sum results obtained with VLOOKUP and IMPORTRANGE in Google Sheets?

Question

I'm currently obtaining the result with the formula below, which was nicely provided by player0, but the challenge now is to obtain not only the figure found, but a sum, since the occurrences in the "database" may be multiple.

=Arrayformula(if(A9:A="";;IFNA(vlookup(A9:A&D9:D&"Expedição"&"Costura";
{IMPORTRANGE("fileId";"Produção!F2:F")&
IMPORTRANGE("fileId-1n3nQ";"Produção!H2:H")&
IMPORTRANGE("fileId-1n3nQ";"Produção!R2:R")&
IMPORTRANGE("fileId-1n3nQ";"Produção!B2:B")\
IMPORTRANGE("fileId-1n3nQ";"Produção!N2:N")};2;0))))

I have tried including sum in many places in the formula above, but with no success.

Here's a file with dummy data.

Thanks in advance.

Answer 1

use:

=ARRAYFORMULA(IF(A4:A="",,IFNA(VLOOKUP(A4:A&" "&C4:C&" Expedição Costura ",
 QUERY(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY({IMPORTRANGE(
 "1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Data Origin!A2:R")}, 
 "select Col6,Col8,Col18,Col2,'×',Col14 where Col14>0 label '×'''", )),,9^9)), "×"), 
 "select Col1,sum(Col2) where Col2>0 group by Col1"), 2, 0))))