SUMIFS使用单元格作为数组条件返回0值问题咨询

根因说明 #

你直接在公式中手写的{"5","10","11"}属于Excel可直接识别的内存数组结构，而存储在E1单元格中的{"5","10","11"}是普通文本字符串，没有被解析为数组。SUMIFS读取E1内容时会将其作为单个完整文本值去匹配'POS Data'!$B:$B列的内容，自然找不到匹配项，所以返回结果为0。

可行解决方法 #

方法1：文本转数组（适用于Excel 365/2021及以上版本） #

通过文本拆分函数把E1的文本字符串转换为可被识别的数组：

=SUM(SUMIFS('POS Data'!$G:$G,'POS Data'!$B:$B,SUBSTITUTE(TEXTSPLIT(MID(E1,2,LEN(E1)-2),","),"""","")))

逻辑说明：

• 先用MID(E1,2,LEN(E1)-2)去掉文本前后的大括号
• 再用TEXTSPLIT按逗号拆分得到每个带双引号的元素
• 最后用SUBSTITUTE删除每个元素的双引号，得到目标数组

方法2：XML解析转数组（适用于Excel 2013及以上版本） #

没有TEXTSPLIT的旧版本可以用FILTERXML实现数组转换：

=SUM(SUMIFS('POS Data'!$G:$G,'POS Data'!$B:$B,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1,"{""",""),"""}",""),""",""","</s><s>")&"</s></t>","//s")))

方法3：优化存储规则（所有版本通用） #

如果可以调整存储方式，不要把多个条件拼在同一个单元格里，将"5"、"10"、"11"分别存储在E1、E2、E3单元格，直接引用单元格区域作为条件即可：

=SUM(SUMIFS('POS Data'!$G:$G,'POS Data'!$B:$B,E1:E3))

注：2019及更早版本使用该公式时，需要按Ctrl+Shift+Enter组合键触发数组运算，365/2021版本直接回车即可生效。

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