含空列的多列+单行条件SUMPRODUCT计算问题求助

问题根源 #

空列C打乱了数据区域的列对应关系，让原公式里的品牌、产品条件和D列数值、G:I年份列的行关联错位，最终算出0。

修正公式（两种可选） #

假设你的数据结构是：品牌在B列，C是空列，产品在E列，D列是要参与计算的数值，G:I列是不同年份的数据（表头G1:I1为年份），K2是目标年份，L2:L4是需匹配的品牌，M2:M4是需匹配的产品。

方案1：精准定位年份列（推荐） #

=SUMPRODUCT(
  --ISNUMBER(MATCH(B:B, L2:L4, 0)),
  --ISNUMBER(MATCH(E:E, M2:M4, 0)),
  D:D,
  INDEX(G:I, 0, MATCH(K2, G1:I1, 0))
)

• 用MATCH(K2, G1:I1, 0)定位目标年份在G:I中的列位置，再通过INDEX提取整列数据，彻底避开空列C的干扰
• --ISNUMBER(MATCH(...))用来判断每行的品牌/产品是否在指定范围内，符合条件返回1，否则返回0

方案2：限定数据行范围（提升计算效率） #

如果整列引用导致计算卡顿，换成实际数据行范围（比如数据到第100行）：

=SUMPRODUCT(
  (B2:B100=L2:L4)*(E2:E100=M2:M4),
  D2:D100,
  INDEX(G2:I100, 0, MATCH(K2, G1:I1, 0))
)

注意事项 #

• 确保G1:I1的年份表头和K2的年份格式完全一致（别出现一个是数字、一个是文本的情况）
• 检查品牌、产品列和L2:L4、M2:M4的内容，避免空格、大小写差异这类隐性匹配问题
• 代入示例数据后，公式应返回预期结果191.5，若仍有异常，核对各列的对应关系是否正确

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