使用SUMPRODUCT+TEXTSPLIT+MATCH求和多交叉值时返回#VALUE!错误

错误原因 #

你的公式返回#VALUE!主要源于两个核心问题：

1. 数组维度不匹配：TEXTSPLIT拆分逗号分隔字符串默认返回水平数组，但匹配行的范围BN2:BN10是垂直数组，MATCH在跨维度数组间匹配时会生成错误值，导致SUMPRODUCT无法完成运算。
2. 空格干扰匹配：拼接字符串或单元格内容可能存在前后空格，导致MATCH无法精准匹配，返回错误值后触发SUMPRODUCT报错。

修正后的公式 #

=SUMPRODUCT(
    ISNUMBER(MATCH(TRIM(BN2:BN10), TOCOL(TEXTSPLIT(TRIM(BO28), ", "), 1), 0)) *
    ISNUMBER(MATCH(TRIM(BN1:BU1), TEXTSPLIT(TRIM(BP28), ", "), 0)) *
    BN2:BU10
)

关键调整说明 #

• TRIM()：去除所有单元格内容的前后空格，彻底避免因空格导致的匹配失败。
• TOCOL(..., 1)：将TEXTSPLIT返回的水平数组转换为垂直数组，同时忽略拆分产生的空值，确保行匹配时MATCH的两个参数维度一致。
• 列匹配无需转置：列标题范围BN1:BU1是水平数组，TEXTSPLIT拆分列拼接字符串后也是水平数组，维度天然匹配。

额外排查点 #

若修正后仍报错，检查以下内容：

• 确认BN2:BU10区域内所有单元格都是数值类型，无文本或空值干扰。
• 验证BO28和BP28的拼接字符串格式规范，无多余逗号或特殊字符。

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