多条件计数请教

a1210517448

hxj119

啥意思，描述一下？

a1210517448

hxj119 发表于 2024-9-30 10:54
啥意思，描述一下？

E1.2.3.4.5.6.7.8，25,26栋一个区域
E9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.27.28.29.30.31一个区域

hxj119

1. =LET(a,"E"&DROP(REDUCE("",REGEXP(C2,"[\d-]+"),LAMBDA(x,y,VSTACK(x,ROW(INDIRECT(SUBSTITUTE(y,"-",":")))))),1),REDUCE(0,$A$2:$A$32,LAMBDA(m,n,m+SUM((TEXTBEFORE(n,"-")=a)/1))))

复制代码

苏小亓

1. =SUM(--(TEXTBEFORE($A$2:$A$32,"-")=TRANSPOSE("E"&ROW(INDIRECT(TEXTAFTER(SUBSTITUTE(C2,"-",":E"),"&"))))),--(TEXTBEFORE($A$2:$A$32,"-")=TRANSPOSE("E"&ROW(INDIRECT(TEXTBEFORE(SUBSTITUTE(C2,"-",":E"),"&"))))))

复制代码

hxj119

自由填写区域，优化一下公式

1. =LET(a,"E"&DROP(REDUCE("",REGEXP(C2,"[\d-]+"),LAMBDA(x,y,VSTACK(x,ROW(INDIRECT(SUBSTITUTE(y,"-",":")))))),1),REDUCE(0,$A$2:$A$32,LAMBDA(m,n,m+SUM((TEXTBEFORE(n,"-")=a)/1))))

复制代码

ovxyz

=COUNT(MATCH(REGEXEXTRACT($A$2:$A$32,"\d+")*1,REDUCE("",TEXTSPLIT(SUBSTITUTE(C2,"E",""),"&"),LAMBDA(s,x,VSTACK(s,LET(a,TEXTSPLIT(x,"-"),IF(ISERROR(INDEX(a,2)),x*1, SEQUENCE(INDEX(a,2)-INDEX(a,1)+1,,INDEX(a,1))))))),0))
支持更多条件，支持单个。

laomu2010

=LET(A,FREQUENCY(--TEXTBEFORE(TEXTAFTER($A$2:$A$32,"E"),"-"),--REGEXEXTRACT(C2,"[\d]+",1)),INDEX(A,2)+INDEX(A,4)+2)

a1210517448

hxj119 发表于 2024-9-30 11:11
自由填写区域，优化一下公式

如果新增数据怎么办呢

hxj119

a1210517448 发表于 2024-9-30 12:01
如果新增数据怎么办呢

9999行，这样区域够大了
更新公式：

1. =LET(a,"E"&DROP(REDUCE("",REGEXP(C4,"[\d-]+"),LAMBDA(x,y,VSTACK(x,ROW(INDIRECT(TEXTJOIN(":",,"AA"&TEXTSPLIT(y,"-"))))))),1),REDUCE(0,TOCOL($A$2:$A$9999,1),LAMBDA(m,n,m+SUM((TEXTBEFORE(n,"-")=a)/1))))

复制代码