多条件计数

kkdevil04

看图求解，文件已上传

yfan17

1. =COUNTIFS($B$4:$F$7,$A12,$C$4:$G$7,SUBSTITUTE(B$11,"次数",))

复制代码

静听溪流

=COUNTIFS($B$4:$G$7,$A12,$C$4:$H$7,IF(LEFT(B$11)="不","不","")&"达标")

kkdevil04

yfan17 发表于 2023-6-12 20:00

有个地方没想通，为什么两个条件区域不能是一致的呢

kkdevil04

静听溪流 发表于 2023-6-12 20:07
=COUNTIFS($B$4:$G$7,$A12,$C$4:$H$7,IF(LEFT(B$11)="不","不","")&"达标")

有个地方没想通，为什么两个条件区域不能是一致的呢

橒♂蝣

=LET(x,IF(ISNUMBER(XMATCH($B$4:$G$7,$A12)),MOD(SEQUENCE(ROWS($B$4:$G$7),COLUMNS($B$4:$G$7),1,1)+5,6)+1)+1,
y,LARGE(x,SEQUENCE(COUNT(XMATCH($B$2:$G$7,$A12)))),
a,IF(ISNUMBER(XMATCH($B$4:$G$7,$A12)),INT(SEQUENCE(ROWS($B$4:$G$7),COLUMNS($B$4:$G$7),1,1)/6)+1),
bb,LARGE(a,SEQUENCE(COUNT(XMATCH($B$2:$G$7,$A12)))),
SUMPRODUCT(1*(INDEX($B$4:$G$7,bb,y)=LEFT(B$11,FIND("标",B$11))))
)