统计每个月里出现出现3次及3次以上的姓名个数

天涯论剑

要求：统计每个月里出现出现3次及3次以上的姓名个数

huaichuan1

F2=IF(MAX(IF(MONTH($A$2:$A$19)=F1,COUNTIF($B$2:$B$19,$B$2:$B$19)))>=3,MAX(IF(MONTH($A$2:$A$19)=F1,COUNTIF($B$2:$B$19,$B$2:$B$19))),0)

part98

=LET(s,GROUPBY($B$2:$B$10,1*(MONTH($A$2:$A$10)=F1),SUM,0,0),SUM(IF(TAKE(s,,-1)>=3,1,0)))

hxj119

=LET(a,FILTER($B$2:$B$10,MONTH($A$2:$A$10)=F$1),b,TRANSPOSE(UNIQUE(a)),IFERROR(SUM(N(BYCOL(N(a=b),LAMBDA(x,SUM(x)))>=3)),0))

hugeinfo

=SUM(N(LEN(TEXTSPLIT(CONCAT(N(IF(MONTH($A$2:$A$10)=F$1,COUNTIF($B$2:$B$10,$B$2:$B$10),0)>=3)),,0))>=3))

无关岁月

1. =LET(_a,MONTH($A$2:$A$10),_b,GROUPBY($B$2:$B$10,_a,COUNT,,0,,_a=F$1),COUNT(0/(INDEX(_b,,2)>2)))

复制代码

袁勇

低版本。

1. =ROUND(SUMPRODUCT(1/TEXT(COUNTIFS($B:$B,$B2:$B100,$A:$A,">="&DATE(2024,F1,1),$A:$A,"<="&DATE(2024,F1+1,1)),"[<3]9!e37;0")),0)

复制代码

windshc

=LET(a,FILTER($B$2:$B$19,MONTH($A$2:$A$19)=G1),b,SEQUENCE(COUNTA(a),,,0),c,GROUPBY(a,b,COUNTA,,0),d,IFERROR(SUM(IF(CHOOSECOLS(c,2)>=3,1,0)),""),d)

quqiyuan

1. =COUNT(0/(MMULT(COLUMN($A:$I)^0,N(MATCH($B$2:$B$10,$B$2:$B$10,)*(MONTH($A$2:$A$10)=F1)=COLUMN($A:$Z)))>=3))

复制代码

秃头发师

=SUM(--(COUNTIF($B$2:$B$10,UNIQUE(IF(MONTH($A$2:$A$10)=F1,$B$2:$B$10,"")))>2))