基于多列与单行条件计算Excel SUMPRODUCT函数值

需求逻辑 #

需要计算符合以下所有条件的单元格数值与对应百分比的乘积之和：

• 列条件：表头区域C1:G1等于单元格I2的值（即取该列的百分比）
• 行条件：A2:A12的值在J2:J3范围内，且B2:B12的值在K2:K3范围内（即取该行的数值）
• 最终结果为「符合条件的数值 × 对应百分比」的总和，示例结果：500×7% + 90×5% = 39.5

替换SUM(IF...)的SUMPRODUCT公式 #

直接使用SUMPRODUCT即可实现，新版Excel无需数组输入（旧版Excel需按Ctrl+Shift+Enter确认）：

=SUMPRODUCT((C1:G1=I2)*(COUNTIF(J2:J3,A2:A12))*(COUNTIF(K2:K3,B2:B12)),C2:G12)

公式拆解 #

1. (C1:G1=I2)：生成横向数组，表头等于I2的位置返回TRUE（等价于1），其余为FALSE（等价于0），用于定位目标百分比列
2. COUNTIF(J2:J3,A2:A12)：生成纵向数组，A列值在J2:J3范围内的行返回1，否则返回0，筛选符合A列条件的行
3. COUNTIF(K2:K3,B2:B12)：同理生成纵向数组，筛选符合B列条件的行
4. 三个条件数组相乘后，只有同时满足所有条件的单元格位置会得到1，其余为0；最后与C2:G12的数值区域相乘，SUMPRODUCT自动求和所有有效乘积

简化写法（效果一致） #

=SUMPRODUCT((C1:G1=I2)*(COUNTIF(J2:J3,A2:A12)>0)*(COUNTIF(K2:K3,B2:B12)>0)*C2:G12)

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