如何用Sumif+Index/Match计算选中选项的成本总和（无辅助列）

针对你的需求，我整理了两种无需辅助列的公式方案，分别适配不同版本的Excel：

1. 兼容所有Excel版本的SUMPRODUCT方案 #

先明确一下假设的表结构（你可以根据实际数据调整单元格范围）：

• 工作表1（暂称「选项表」）：
  • A列：选项名称
  • B列：是否选中（Yes/No）
  • C列：选项对应的关联值（用来匹配行项目表）
• 工作表2（暂称「行项目表」）：
  • A列：行项目名称
  • B列：关联选项值（和选项表C列对应）
  • C列：行项目成本

在工作表2的C8单元格输入以下公式：

=SUMPRODUCT((选项表!$B$2:$B$10="Yes")*(行项目表!$B$2:$B$20=选项表!$C$2:$C$10)*行项目表!$C$2:$C$20)

公式逻辑： #

• (选项表!$B$2:$B$10="Yes")：筛选出选项表中标记为"Yes"的行，返回一组TRUE/FALSE数组
• (行项目表!$B$2:$B$20=选项表!$C$2:$C$10)：判断行项目表的每个选项值是否匹配Yes选项的关联值，返回另一组TRUE/FALSE数组
• 行项目表!$C$2:$C$20：取行项目表的成本数组
• SUMPRODUCT会将三个数组对应位置相乘（TRUE等价于1，FALSE等价于0），最后对所有结果求和，自动忽略不符合条件的行。

2. Excel 365/2021+ 动态数组方案（更直观） #

如果你使用的是支持动态数组的Excel版本，这个公式可读性更强：

=SUM(FILTER(行项目表!$C$2:$C$20,ISNUMBER(XMATCH(行项目表!$B$2:$B$20,FILTER(选项表!$C$2:$C$10,选项表!$B$2:$B$10="Yes")))))

公式逻辑： #

1. 内层FILTER(选项表!$C$2:$C$10,选项表!$B$2:$B$10="Yes")：提取所有标记为"Yes"的选项关联值，生成一个动态数组
2. XMATCH(行项目表!$B$2:$B$20, ...)：判断行项目表的每个选项值是否存在于上述数组中，存在则返回位置，不存在返回错误值
3. ISNUMBER(...)：将XMATCH的结果转为TRUE/FALSE（存在为TRUE，不存在为FALSE）
4. 外层FILTER(行项目表!$C$2:$C$20, ...)：筛选出符合条件的行项目成本
5. SUM(...)：对筛选后的成本求和

注意事项： #

• 请根据你的实际数据范围调整公式中的单元格区域（比如选项表的$B$2:$B$10、行项目表的$B$2:$B$20等）
• 两种方案都支持一个选项对应多个行项目的场景，会自动累加所有匹配的成本

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