SUMPRODUCT结合SUBTOTAL处理筛选值时公式报错问题排查

核心问题：数组维度不匹配 #

你的公式里三个部分的数组维度完全不兼容：

• COUNTIF(M5:M500, {"KE", "BF", "LF", "SOP", "ME", "ME+", "PS"}) 返回的是横向7元素数组（对应7个关键词的总出现次数）
• HLOOKUP(...) 返回的也是横向7元素数组（对应每个关键词的转换值）
• SUBTOTAL(3, OFFSET(M5, ROW(M5:M500)-ROW(M5), 0)) 返回的是纵向496元素数组（对应M5:M500每一行的可见状态）

SUMPRODUCT无法将横向数组和纵向数组直接相乘，必然产生错误值，这就是公式失效的原因。另外，原公式用COUNTIF统计关键词总次数的逻辑也不对——你需要的是逐行判断是否匹配关键词，再结合可见行标记求和，而不是统计关键词的总出现次数。

修正后的公式 #

基础版 #

=SUMPRODUCT(
  --ISNUMBER(MATCH(M5:M500, {"KE", "BF", "LF", "SOP", "ME", "ME+", "PS"}, 0)),
  INDEX(SIZING!$D$1:$D$8, MATCH(M5:M500, SIZING!$A$1:$A$8, 0)),
  SUBTOTAL(3, OFFSET(M5, ROW(M5:M500)-ROW(M5), 0))
)

带错误处理版（避免不匹配行的错误值） #

=SUMPRODUCT(
  IFERROR(--ISNUMBER(MATCH(M5:M500, {"KE", "BF", "LF", "SOP", "ME", "ME+", "PS"}, 0)) * INDEX(SIZING!$D$1:$D$8, MATCH(M5:M500, SIZING!$A$1:$A$8, 0)), 0),
  SUBTOTAL(3, OFFSET(M5, ROW(M5:M500)-ROW(M5), 0))
)

公式逻辑说明 #

1. --ISNUMBER(MATCH(...))：逐行检查M列单元格值是否在目标关键词列表中，匹配返回1，不匹配返回0，生成和M5:M500同维度的纵向数组。
2. INDEX(SIZING!$D$1:$D$8, MATCH(...))：逐行取出当前M列值对应的SIZING表中Analytics Data列的转换值，同样是纵向数组。
3. SUBTOTAL(3, OFFSET(...))：逐行标记当前行是否可见（可见返回1，隐藏返回0），保证只计算筛选后的行。
4. SUMPRODUCT会将三个同维度数组的对应元素相乘，最后求和，自动忽略不匹配行产生的错误值（带IFERROR的版本则直接将错误值转为0）。

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