MS Office2016含文本数据时如何按横纵双条件求和

问题核心是求和区域混杂文本时，常规SUMPRODUCT()直接做数组乘法会把文本识别为错误值导致计算失败，以下两个方案完全兼容MS Office 2016，不需要依赖高版本专属函数：

方案1：零容错优先方案 #

用SUMIFS()+INDEX()+MATCH()组合实现，SUMIFS本身会自动忽略求和区域内的文本内容，哪怕求和区混有汉字、符号、错误值标记都不会触发计算报错：

=SUMIFS(INDEX(数据区域,0,MATCH(横向条件值,横向表头行,0)),纵向条件列,纵向条件值)

对应常规表格结构的写法示例：

• 纵向维度（如品类、部门）在A列，有效数据范围为A2:A100
• 横向维度（如月份、指标）在第1行，有效表头范围为B1:M1
• 交叉数据范围为B2:M100，区域内混杂文本标注
• 需求为匹配纵向值「品类A」、横向值「3月」的数值和，公式直接写：
  =SUMIFS(INDEX(B:M,0,MATCH("3月",B1:M1,0)),A:A,"品类A")
  公式逻辑：先用MATCH定位横向条件在表头中对应的列序号，再用INDEX取出该列的全部数据，最后交给SUMIFS按纵向条件筛选求和，非数值内容会被自动跳过。

方案2：SUMPRODUCT改造方案 #

如果习惯使用SUMPRODUCT逻辑，加一层容错转换把非数值内容转为0即可，不需要调整原有公式的判断框架：

=SUMPRODUCT((纵向条件列=纵向条件值)*(横向表头行=横向条件值)*IFERROR(--数据区域,0))

同样以上述场景为例，公式写法为：
=SUMPRODUCT((A2:A100="品类A")*(B1:M1="3月")*IFERROR(--B2:M100,0))
公式中--的作用是将文本格式存储的数字转为可计算的数值类型，纯文本内容会被识别为错误值，经IFERROR转换为0后不参与求和计算。

参考数据示例 #

使用注意 #

• 公式内的单元格范围请根据实际表格的行列位置调整，数据量大时尽量缩小引用范围到实际数据所在行，避免引用整列拖慢计算速度
• 条件区域不要包含合并单元格，否则会出现匹配错位
• 如需匹配多组横/纵向条件，直接在对应函数内追加条件判断参数即可

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