如何在Google表中使用Vlookup和Ingortrange获得的总和结果？

发布于 2025-01-30 04:15:03 字数 487 浏览 4 评论 0原文

我目前正在获得以下公式的结果，该公式由Player0很好地提供，但是现在的挑战不仅要获得图形，而且要获得一个总和，因为“数据库”中的出现可能是多个。

=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'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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

遮了一弯 2025-02-06 04:15:03

使用：

=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))))

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))))