如何在Excel中结合SUMIF与IF实现双变量多条件结果求和

场景说明 #

• 数据源：A:D区域，每日新增多条记录，核心数据在C列（Distance）、D列（Cost）
• 目标：制作F:J区域的双变量表格，表头为Distance阈值（如10、25...），列标题为Cost阈值（如-15、-10...），每个单元格需计算所有行的判断总和：

  每行满足AND(Dn>Cost阈值, Cn>=Distance阈值)时加10，否则加-10

当前问题 #

• 用SUMIF仅能计算符合条件的正值总和，无法直接包含不符合条件的负值
• 反向计算时，会将同时不满足两个条件的记录重复扣减（计为2次-10，实际应为1次）
• 当前需为每个条件组合新增辅助列，效率低下

无辅助列解决方案 #

直接在双变量表格的单元格中使用以下简化公式（以F2单元格为例，对应表头F1的Distance阈值、列标题E2的Cost阈值）：

=SUMPRODUCT(20*(($D$3:$D$100>E2)*($C$3:$C$100>=F1)) - 10*ROWS($D$3:$D$100))

公式逻辑 #

1. ($D$3:$D$100>E2)*($C$3:$C$100>=F1)：生成数组，满足双条件的行返回1，否则返回0
2. 乘以20：满足条件的行比不满足的行多20分（10 - (-10)）
3. 减去10*ROWS($D$3:$D$100)：先给所有行默认扣10分（对应不满足条件的情况），满足条件的行再加20分，最终正好是满足得10、不满足得-10

使用方式 #

• 将公式中的$D$3:$D$100和$C$3:$C$100替换为实际的数据行范围
• 输入F2单元格后，横向、纵向拖拽即可覆盖F:J区域的所有条件组合，无需额外辅助列

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