Excel如何按行内指定离职日期条件求和对应相邻离职福利

通用兼容方案（支持Excel 2007及所有后续版本，无需新函数） #

使用SUMPRODUCT函数批量匹配计算，无需枚举列，效率足够支撑1000行*100列的规模：

1. 先定义单元格位置：
   • 目标统计年份放在单独单元格，例如A1（输入2040即统计2040年的福利总和）
   • 单行数据的所有「离职日期+福利」列范围，以示例第一行模拟数据为例是B2:G2，实际100列可修改为对应范围（例如B2:CY2）
2. 输入公式：

=SUMPRODUCT((B2:G2=A1)*IFERROR(--C2:H2,0)*(MOD(COLUMN(B2:G2)-COLUMN(B2),2)=0))

公式逻辑说明：

• (B2:G2=A1)：匹配所有等于目标年份的离职日期单元格
• MOD(COLUMN(B2:G2)-COLUMN(B2),2)=0：限定只匹配第1、3、5…奇数位的列（对应所有「Leaving Date」列的固定位置，符合每组日期+福利间隔排列的规则）
• IFERROR(--C2:H2,0)：自动取匹配日期列右侧相邻的福利值，空单元格、非数值内容自动转为0，带格式的$金额也可正常识别
• 最终自动把所有符合条件的福利值求和，下拉公式即可批量计算1000行的结果

Excel 365/2021 精简方案 #

如果你使用的是支持LAMBDA函数的新版本Excel，可以用更简洁的遍历逻辑：

=SUM(BYCOL(SEQUENCE(,50,2,2),LAMBDA(x,IF(INDEX(2:2,x)=A1,IFERROR(--INDEX(2:2,x+1),0),0))))

其中SEQUENCE(,50,2,2)里的50替换为你实际的ID总数即可，公式会自动遍历所有ID的离职日期列，匹配求和。

补充说明 #

• 如果你的福利值是手动输入的带$文本，可将公式中--C2:H2替换为--SUBSTITUTE(C2:H2,"$","")即可正常提取数值
• 如需直接统计所有1000行的全年份总福利，直接扩大公式的行范围即可，例如统计所有行2040年总福利：

=SUMPRODUCT((B2:G1001=A1)*IFERROR(--C2:H1002,0)*(MOD(COLUMN(B2:G1001)-COLUMN(B2),2)=0))

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