多列匹配后根据多个条件求和

RIYUEXING84

麻烦各位大神帮忙写条公式，多列数据匹配后，根据多个条件求和有效次数。Thanks。。。。。。

溺水鱼

1. =LET(a,FILTER(A2:C38,ISNUMBER(MATCH(B2:B38,E2:E11,))*(C2:C38>=5)),GROUPBY(INDEX(a,,1),INDEX(a,,1),COUNTA,,0))

复制代码

溺水鱼

1. =SUM(COUNTIFS($A$2:$A$38,G2,$B$2:$B$38,$E$2:$E$11,$C$2:$C$38,">=5"))

复制代码

RIYUEXING84

溺水鱼 发表于 2024-11-25 13:42

好用，谢谢。。。

袁勇

??

1. =SUMPRODUCT(COUNTIFS(A:A,G2,B:B,E$2:E$11,C:C,">=5"))

复制代码

zwzc

=DROP(GROUPBY(HSTACK(MATCH(A2:A38,A2:A38,),A2:A38),C2:C38,COUNT,0,0,,BYROW(N(B2:B38=TRANSPOSE(E2:E11)),SUM)*(C2:C38>=5)),,1)

hugeinfo

h2=SUM(N(FILTER($A$2:$A$38,BYROW(N($B$2:$B$38=TRANSPOSE($E$2:$E$11)),LAMBDA(a,SUM(a)))*($C$2:$C$38>=5))=G2))

RIYUEXING84

感谢各位大神热心相助~

hcm19522

=SUMPRODUCT((A$2:A$38=G2)*COUNTIF(E:E,B$2:B$38)*(C$2:C$38>=5))

你是那块小饼干

=GROUPBY(A2:A38,A2:A38,COUNTA,0,0,,BYROW(N(B2:B38=TRANSPOSE(E2:E11)),SUM)*BYROW(N(A2:A38=TRANSPOSE(G2:G7)),SUM)*(C2:C38>=5))