无辅助列/工作表跨表格拖拽统计数据的公式报错求助

问题背景 #

• 约束：禁止使用辅助列/工作表，仅基于现有内容操作
• 现象：同表格内跨工作表的SUMIFS公式可正常运行，但跨不同电子表格时，使用IMPORTRANGE嵌套SUMIFS会报错参数必须为范围
• 报错公式：

=ARRAYFORMULA(SUMIFS(
 IMPORTRANGE("1JEYiK3etpIRSpd6BloVvwmGEhhi_5uAE_YBFmHPbLLg", "Event Logs!$I$8:$I"),
 IMPORTRANGE("1JEYiK3etpIRSpd6BloVvwmGEhhi_5uAE_YBFmHPbLLg", "Event Logs!$G$8:$G"), "Patrol",
 IMPORTRANGE("1JEYiK3etpIRSpd6BloVvwmGEhhi_5uAE_YBFmHPbLLg", "Event Logs!$K$8:$K"), $E$10:$E,
 IMPORTRANGE("1JEYiK3etpIRSpd6BloVvwmGEhhi_5uAE_YBFmHPbLLg", "Event Logs!$F$8:$F"), "Contracted"
))

• 同表格正常运行的对比公式：

=SUMIFS('Event Logs'!$E$3:$E, 'Event Logs'!$C$3:$C, "Patrol", 'Event Logs'!$G$3:$G, $A$5:$A, 'Event Logs'!$B$3:$B, "Contracted")

问题原因 #

SUMIFS函数的条件参数要求必须是原生单元格范围，而IMPORTRANGE返回的是内存数组，并非实际的单元格范围，因此直接嵌套会触发“参数必须为范围”的错误。

解决方案 #

方案1：使用SUMPRODUCT替代SUMIFS（支持数组运算） #

SUMPRODUCT可以直接处理IMPORTRANGE返回的数组，通过逻辑判断转换为0/1值实现条件求和：

=ARRAYFORMULA(
 SUMPRODUCT(
  IMPORTRANGE("1JEYiK3etpIRSpd6BloVvwmGEhhi_5uAE_YBFmHPbLLg", "Event Logs!$I$8:$I"),
  --(IMPORTRANGE("1JEYiK3etpIRSpd6BloVvwmGEhhi_5uAE_YBFmHPbLLg", "Event Logs!$G$8:$G")="Patrol"),
  --(IMPORTRANGE("1JEYiK3etpIRSpd6BloVvwmGEhhi_5uAE_YBFmHPbLLg", "Event Logs!$K$8:$K")=$E$10:$E),
  --(IMPORTRANGE("1JEYiK3etpIRSpd6BloVvwmGEhhi_5uAE_YBFmHPbLLg", "Event Logs!$F$8:$F")="Contracted")
 )
)

注：--用于将布尔值（TRUE/FALSE）转换为数值1/0，确保SUMPRODUCT可以正确计算乘积和。

方案2：用LET函数减少IMPORTRANGE重复调用（提升效率） #

多次调用IMPORTRANGE会降低公式运行效率，使用LET一次性导入所需数据范围，再通过INDEX提取对应列进行计算：

=ARRAYFORMULA(
 LET(
  // 一次性导入F到K列的所有数据（包含所有条件列和求和列）
  raw_data, IMPORTRANGE("1JEYiK3etpIRSpd6BloVvwmGEhhi_5uAE_YBFmHPbLLg", "Event Logs!$F$8:$K"),
  // 提取对应列：Col4=I列（求和列），Col2=G列（Patrol条件），Col6=K列（匹配E10:E），Col1=F列（Contracted条件）
  sum_values, INDEX(raw_data,,4),
  cond_patrol, INDEX(raw_data,,2)="Patrol",
  cond_match, INDEX(raw_data,,6)=$E$10:$E,
  cond_contracted, INDEX(raw_data,,1)="Contracted",
  // 计算符合所有条件的求和值
  SUMPRODUCT(sum_values * cond_patrol * cond_match * cond_contracted)
 )
)

方案3：使用QUERY函数（简洁灵活） #

QUERY函数支持直接对IMPORTRANGE返回的数组进行筛选求和，语法更直观：

=ARRAYFORMULA(
 QUERY(
  IMPORTRANGE("1JEYiK3etpIRSpd6BloVvwmGEhhi_5uAE_YBFmHPbLLg", "Event Logs!$F$8:$K"),
  "SELECT SUM(Col4) WHERE Col2='Patrol' AND Col6='"&$E$10:$E&"' AND Col1='Contracted' LABEL SUM(Col4) ''",
  0
 )
)

注：Col4对应原数据的I列，Col2对应G列，Col6对应K列，Col1对应F列，需根据实际列位置调整。

注意事项 #

1. 首次使用IMPORTRANGE时，需要点击公式旁的「允许访问」按钮，授权当前表格访问目标表格的数据。
2. 若需要批量生成结果，ARRAYFORMULA会自动遍历$E$10:$E的所有值，无需手动下拉公式。

内容的提问来源于stack exchange，提问作者Awexu