统计多行符号（数字）个数

ztsss161511

如题。
有多行数据，每个单元格都有多个逗号和数字。不想使用辅助列。一个公式能否统计出多多行的逗号数量或数字数量。尤哥额外要求就是最好统计范围是B:B条件，因为每次数据有更新行数可能不固定。

小凡、、、

=LEN(CONCAT(B2:B31))-LEN(SUBSTITUTE(CONCAT(B2:B31),",",))
=LEN(CONCAT(B2:B31))-E2

gadxjyang

E2:=SUM(LEN(B2:B31))-SUM(LEN(SUBSTITUTE(B2:B31,",",)))
F2:=E2+ROWS(A1:A31)

xlllw203

F2

1. =COUNTA(TEXTSPLIT(TEXTJOIN(",",,B2:B31),","))

复制代码

E2

1. =F2-ROWS(B2:B31)

复制代码

richardzhu15

=COUNTA(REGEXP(TEXTJOIN("+",,B2:B31),","))
=COUNTA(REGEXP(TEXTJOIN("+",,B2:B31),"\d+"))

hxj119

一个公式：

1. =LET(k,SCAN(0,B2:B9999,LAMBDA(x,y,ROWS(TEXTSPLIT(y,,",")))),VSTACK(E1:F1,HSTACK(SUM(k-1),SUM(k))))

复制代码

1. 
   

复制代码

无关岁月

1. =COUNTA(REGEXP(CONCAT(B2:B31),"\W+"))

复制代码

1. =COUNTA(REGEXP(TEXTJOIN(",",,B2:B31),"\d+"))

复制代码

waternew

=LEN(CONCAT(TEXTSPLIT(B2,ROW($1:$99))))

leepoison

=LET(a,COLUMNS(REGEXP(CONCAT(B2:B31),",")),HSTACK(a,a+1+COUNTA(A2:A31)))

你是那块小饼干

1. =LEN(CONCAT(B2:B31))-LEN(SUBSTITUTE(CONCAT(B2:B31),",",""))

复制代码

1. =COUNT(TEXTSPLIT(TEXTJOIN("",TRUE,B2:B31),",","",TRUE)*1)

复制代码