Google Sheets多条件SUMPRODUCT+IF组合公式实现折扣计算

上下文 #

• 表格包含商品price（B列）、Qty（C列）；E列（exclusions）有内容则该行不参与促销计算
• 折扣表中H列（Minimum）为折扣门槛，I列（discount）为折扣值；需删除仅作演示的D列；操作环境为Google Sheets

H5单元格公式需求 #

1. H列有值则对应折扣激活，无值则无效；无激活折扣或同时激活两个折扣时H5留空
2. 仅激活Discount 1且满足门槛时：计算E列有内容行的SUMPRODUCT(B*C，空白Qty视为1) ÷ 订单总SUMPRODUCT × I2，示例：(B3*1+B6*C6+B8*C8)/D12*I2 = $8/$27*$10 = $2.96
3. 仅激活Discount 2且满足门槛时：计算E列有内容行的SUMPRODUCT(B*C，空白Qty视为1) ÷ 订单总SUMPRODUCT × (I3×订单总SUMPRODUCT)，示例：(B3*1+B6*C6+B8*C8)/D12*(I3*D12) = $8/$27*(0.1*$27)
4. 不同状态结果要求：H2=$20且H3空白时H5=$2.96；H3=$20且H2空白时H5=$0.80；H2、H3均空白或均有值时H5留空

现有进展 #

• 计算订单总价（空白Qty视为1）的公式：=SUMPRODUCT((ISBLANK(C2:C11)+C2:C11),B2:B11)
• 折扣逻辑公式（未完成，存在D列依赖和SUMPRODUCT替换问题）：=IF(COUNTIF(E2:E11,"<>&""")>0=TRUE,IF(AND(H2="",H3=""),"",IF(AND(H2<>"",H3<>""),"",IF(AND(H2<>"",H3="",D12>=H2),SUMIF(E2:E11,"<>&""",D2:D11)/D12*I2,IF(AND(H2="",H3<>"",D12>=H3),SUMIF(E2:E11,"<>&""",D2:D11)/D12*I3*D12)))))

当前问题 #

无法将公式中的SUMIF(E2:E11,"<>&""",D2:D11)替换为仅基于B、C列且仅计算E列有内容行的SUMPRODUCT公式（需满足空白Qty视为1的规则）

解决方案 #

1. 替换用的SUMPRODUCT公式 #

针对E列有内容行，计算空白Qty视为1的总价，公式如下：

=SUMPRODUCT((E2:E11<>"")*(ISBLANK(C2:C11)+C2:C11)*B2:B11)

• 逻辑说明：(E2:E11<>"")筛选出E列有内容的行；(ISBLANK(C2:C11)+C2:C11)将空白Qty转为1，非空白则取原值；最后与B列price相乘后求和。

2. 整合后的完整H5公式 #

结合折扣逻辑，删除D列依赖（直接嵌入订单总价公式），修正语法错误，最终公式：

=LET(
  total, SUMPRODUCT((ISBLANK(C2:C11)+C2:C11)*B2:B11),
  excluded_total, SUMPRODUCT((E2:E11<>"")*(ISBLANK(C2:C11)+C2:C11)*B2:B11),
  discount1_active, H2<>"",
  discount2_active, H3<>"",
  IF(
    NOT(XOR(discount1_active, discount2_active)), "",
    IF(
      discount1_active,
      IF(total>=H2, excluded_total/total*I2, ""),
      IF(total>=H3, excluded_total*I3, "")
    )
  )
)

• 逻辑说明：
  • 用LET定义变量简化公式：total为订单总价，excluded_total为E列有内容行的总价
  • XOR(discount1_active, discount2_active)判断是否仅激活一个折扣，否则返回空
  • Discount2的计算可简化为excluded_total*I3（原公式中excluded_total/total*(I3*total)可约去total）

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