如何用COUNTIF结合SUBSTITUTE统计以X开头且数值在1-5的单元格？

针对你的需求——统计列中以X: 开头、后续数值在1到5之间（含边界）的单元格数量，同时避免空单元格导致的#VALUE!错误，以下是两种可行方案：

1. 兼容新旧Excel的数组公式 #

=SUM(IF((A:A<>"")*ISNUMBER(SEARCH("X: ",A:A))*(--SUBSTITUTE(A:A,"X: ","")>=1)*(--SUBSTITUTE(A:A,"X: ","")<=5),1,0))

• 旧版Excel：输入完成后需按 Ctrl+Shift+Enter 确认数组公式；新版Excel直接回车即可生效。
• 公式逻辑：
  • A:A<>""：先排除空单元格，从根源避免空值引发的计算错误
  • ISNUMBER(SEARCH("X: ",A:A))：判断单元格内容是否包含X: ，筛选出目标格式的条目
  • --SUBSTITUTE(A:A,"X: ","")：移除X: 前缀，用--将剩余文本转换为可计算的数值
  • >=1 和 <=5：限定数值在目标范围内
  • SUM+IF组合：满足所有条件的单元格记为1，否则记为0，最终求和得到符合条件的总数

2. 新版Excel（365/2021+）简洁方案 #

利用动态数组函数FILTER+ROWS实现更直观的统计：

=ROWS(FILTER(A:A,(LEFT(A:A,3)="X: ")*(--TEXTAFTER(A:A,"X: ")>=1)*(--TEXTAFTER(A:A,"X: ")<=5)*(A:A<>"")))

• LEFT(A:A,3)="X: "：直接判断单元格前3个字符是否为X: ，精准匹配前缀
• TEXTAFTER(A:A,"X: ")：提取X: 之后的数值文本，转换为数值后判断范围
• FILTER筛选出所有符合条件的单元格，ROWS统计筛选结果的行数

为什么之前的RIGHT()+LEN()方案失败？ #

因为你的数值位数不固定（比如1是1位、150是3位），用RIGHT取固定长度会导致长数值被截断，无法正确提取完整数值；而基于X: 这个固定分隔符的SUBSTITUTE或TEXTAFTER方法，能适配任意位数的数值，可靠性更高。

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