Excel中提取Pkr后数值并求和：SUMPRODUCT公式出错求修正

问题说明 #

工作表指定区域（如B3:H3）包含数据：500g (Pkr 700)、500g (Pkr 1250)、500g (Pkr 900)、500g (Pkr 350)、Pkr 1000、Pkr 750，需要提取所有"Pkr"后的数值并求和，目标总和为4950。

原公式问题 #

你使用的公式：

=SUMPRODUCT(--ISNUMBER(SEARCH("Pkr", B2:H2)), --MID(B2:H2, SEARCH("Pkr", B2:H2) + 3, LEN(B2:H2)))

错误原因是MID提取时会包含"Pkr"后的所有字符（比如括号），非数字内容无法转换为有效数值，导致求和错误。

正确公式 #

方案1：兼容Excel 2016及以上版本（含365） #

使用TEXTAFTER函数精准提取数字：

=SUMPRODUCT(--TEXTAFTER(B3:H3, "Pkr ", , , 0))

• 解释：TEXTAFTER提取"Pkr "之后的内容，最后一个参数0表示找不到匹配内容时返回0，避免报错；--将文本转为数值，SUMPRODUCT完成求和。

方案2：兼容旧版Excel（无TEXTAFTER函数） #

用SUBSTITUTE+FILTERXML组合筛选数值：

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(B3:H3,"Pkr ","</s><s>"),")","")&"</s></t>","//s[number(.)=.]"))

• 解释：通过SUBSTITUTE把包含数值的片段拆分为XML节点，FILTERXML筛选出纯数值节点，最后用SUMPRODUCT求和。

方案3：基于原公式修改 #

调整MID提取逻辑，只保留连续数字：

=SUMPRODUCT(--ISNUMBER(SEARCH("Pkr", B3:H3)), --MID(B3:H3, SEARCH("Pkr", B3:H3)+4, LOOKUP(9^9,--MID(B3:H3,SEARCH("Pkr",B3:H3)+4,ROW($1:$10)))))

• 解释：用LOOKUP定位"Pkr"后连续数字的长度，确保MID只提取纯数值部分，避免包含非数字字符。

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