COUNTIF公式无法识别多数字单元格，如何统计1-3范围数值？

问题原因 #

COUNTIF(B$2:B$6,"<=3") 只会尝试将整个单元格内容转为数值后判断，遇到“1 & 8”这类包含多个数字的文本时，无法正确提取其中单个数字进行判断，导致漏统计。

解决方案 #

根据你的Excel版本，选择对应的公式：

方案1：适用于Excel 365/2021（支持动态数组和LAMBDA）

通过BYROW结合TEXTSPLIT拆分单元格内的数字，判断是否存在1-3范围内的数值：

=SUMPRODUCT(--(BYROW(B$2:B$6,LAMBDA(cell,OR(1<=--TEXTSPLIT(cell,{" & "," "})<=3)))))

• TEXTSPLIT(cell,{" & "," "})：拆分单元格内容，提取所有数字文本
• --：将数字文本转换为数值
• 1<=...<=3：判断数值是否在1-3区间内
• OR：只要单元格内有一个数值符合条件，就返回TRUE
• BYROW：对B2:B6的每个单元格执行上述判断
• SUMPRODUCT(--(...))：将逻辑值转为1/0后求和，得到符合条件的单元格总数

方案2：适用于旧版Excel（无动态数组支持）

用FILTERXML提取单元格内的数字，再判断是否符合条件：

=SUMPRODUCT(--(MMULT(--(1<=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(B$2:B$6," & ","</s><s>")," ","</s><s>")&"</s></t>","//s")+0<=3),ROW(INDIRECT("1:"&MAX(LEN(B$2:B$6)-LEN(SUBSTITUTE(B$2:B$6," ",""))+1)))^0)>0))

• SUBSTITUTE+FILTERXML：将单元格内容转为XML格式，提取所有数字文本
• +0：将数字文本转为数值
• MMULT+ROW^0：对每个单元格的多个判断结果求和，只要结果>0，说明该单元格存在符合条件的数值
• SUMPRODUCT(--(...))：统计符合条件的单元格数量

额外说明 #

不需要使用正则表达式（Excel公式无原生正则支持，若用正则需借助VBA），上述公式即可满足需求。

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