如何使用Excel SUMIF或SUMPRODUCT按参考列字符串求和构建汇总表

前提约定 #

先说明两个表的默认单元格位置，方便你对应调整：

• 源数据表（表名示例：源数据）：
  • A列：存储Paris Day 1这类组合描述文本，数据范围为A2:A100（可根据实际数据量调整行号）
  • B列：存储对应待求和的数值，数据范围为B2:B100
• 汇总表（当前编辑的表）：
  • A列：行标题为城市名称，如A2为Paris、A3为London
  • 第1行：列标题为日期标识，如B1为Day 1、C1为Day 2

核心公式 #

在汇总表第一个交叉单元格（B2，对应Paris与Day 1的交叉位置）输入以下公式，回车后向右、向下拖拽填充到所有交叉单元格即可完成自动计算：

=SUMPRODUCT(ISNUMBER(SEARCH($A2, 源数据!$A$2:$A$100))*ISNUMBER(SEARCH(B$1, 源数据!$A$2:$A$100))*源数据!$B$2:$B$100)

注意：公式中的$绝对引用符号不要写错，$A2锁列不锁行，保证向下填充时自动切换不同城市；B$1锁行不锁列，保证向右填充时自动切换不同日期；源数据范围加$锁死，避免填充时范围偏移。

逻辑说明 #

1. SEARCH($A2, 源数据!$A$2:$A$100)：在源数据描述列逐行查找当前行的城市名称，匹配到返回位置数值，匹配失败返回错误值
2. ISNUMBER()将上述结果转换为逻辑值：匹配成功为TRUE（对应数值1），匹配失败为FALSE（对应数值0）
3. 两个ISNUMBER的结果相乘：只有同时包含当前城市和当前日期的行，乘积结果为1，其余情况为0
4. 最后乘以源数据的数值列，通过SUMPRODUCT求和，即可得到所有匹配行的数值总和

可选调整 #

• 需要区分大小写匹配时，将公式中的SEARCH替换为FIND即可
• 若源数据和汇总表在同一个工作表，去掉公式中的源数据!前缀即可
• 可以将源数据转换为Excel表（选中源数据按Ctrl+T），用表列名替代固定行号范围，后续新增源数据时无需手动调整公式范围

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