Excel 2019中SUMPRODUCT条件求和值域及空单元格错误规避

需求背景 #

需在Excel 2019中实现以下功能：

• 对符合条件（A列值为"X"）的多个值域求和，输出最小和-最大和的格式结果
• 错误预防要求：
  • 若B列单元格为单个数字（非值域格式），将其视为自身构成的值域（即最小值=最大值=该数字）
  • 规避B列空单元格（值为""而非0）导致的SUMPRODUCT函数报错

当前问题 #

如何在现有公式中加入上述错误预防机制？

当前使用公式 #

=SUMPRODUCT((A2:A6="X")*(LEFT(B2:B6,FIND("-",B2:B6&"-")-1))) &"-"& 
 SUMPRODUCT((A2:A6="X")*(MID(B2:B6,IFERROR(FIND("-",B2:B6),0)+1,10)))

尝试用IFERROR调整但未成功。

修正方案 #

适配Excel 2019的最终公式（支持LET函数） #

=LET(
    条件区,A2:A6,
    值域区,B2:B6,
    有效值域,FILTER(值域区,条件区="X"),
    最小和,SUMPRODUCT(--IFERROR(LEFT(有效值域,IFERROR(FIND("-",有效值域)-1,LEN(有效值域))),0)),
    最大和,SUMPRODUCT(--IFERROR(MID(有效值域,IFERROR(FIND("-",有效值域)+1,1),LEN(有效值域))),0)),
    最小和&"-"&最大和
)

核心处理逻辑 #

• 空单元格规避：通过IFERROR(...,0)将空值或无法解析的内容转换为0，彻底避免SUMPRODUCT因文本/空值报错
• 单个数字适配：
  • 取最小值时，若单元格中无"-"，则取单元格全部内容（用LEN(有效值域)定位截取长度），保证单个数字的最小值为自身
  • 取最大值时，若单元格中无"-"，则从第1位开始截取全部内容，保证单个数字的最大值与最小值一致
• 数值转换：用--将文本格式的数字强制转换为数值型，确保SUMPRODUCT能正确执行求和计算
• 计算简化：先通过FILTER筛选出符合条件的行，减少后续计算量，提升公式效率

无LET函数的兼容版（纯SUMPRODUCT写法） #

若你的Excel 2019未启用LET函数，可使用此版本：

=SUMPRODUCT((A2:A6="X")*--IFERROR(LEFT(B2:B6,IFERROR(FIND("-",B2:B6)-1,LEN(B2:B6))),0)) &"-"& 
 SUMPRODUCT((A2:A6="X")*--IFERROR(MID(B2:B6,IFERROR(FIND("-",B2:B6)+1,1),LEN(B2:B6)),0))

此版本直接在原公式基础上修改，核心错误预防逻辑与上述LET版本完全一致，仅未封装变量。

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