google-sheets - "Query Completed with an Empty Output"在没有结果时如何让它置零

Question

我的查询产生错误

#N/A Query Completed with an Empty Output.

=sum((query(importrange("mysheet","CR"),"select Col7 where Col1 ='"&A4&"' and Col2 ='"&B4&"' and Col13!='Cancelled' and Col13!='Rejected' ",0)))

当引用的工作表中存在数据时，该功能正常工作。问题是当我将其拖下时，CR 数据集中不存在某些列引用。如何获得零来填充而不是错误？

Answer 1

您需要使用 IFERROR像这样，如果发生错误将导致 0:

=SUM(IFERROR(QUERY(IMPORTRANGE("mysheet","CR"), 
 "select Col7 
  where Col1 ='"&A4&"' 
    and Col2 ='"&B4&"' 
    and Col13!='Cancelled' 
    and Col13!='Rejected' ", 0)))