统计部门人数

baoxiliao

在不拆分上面表格里合并单元格的情况下如何统计各部门人数

hugeinfo

=SUM((LOOKUP(ROW($3:$29),ROW($3:$29)/($B$3:$B$29<>""),$B$3:$B$29)=B33)*($C$3:$C$29<>""))

baoxiliao

hugeinfo 发表于 2023-6-7 17:00
=SUM((LOOKUP(ROW($3:$29),ROW($3:$29)/($B$3:$B$29""),$B$3:$B$29)=B33)*($C$3:$C$29""))

不行，取不到值

hugeinfo

baoxiliao 发表于 2023-6-7 17:10
不行，取不到值

三键
                     

m678

hugeinfo 发表于 2023-6-7 17:26
三键

好像高版本不用三键

逐渐陌生

参考公式=LET(m,SCAN(0,B3:B29,LAMBDA(x,y,IF(y="",x,y))),u,UNIQUE(m),HSTACK(SEQUENCE(COUNTA(u)),u,MAP(u,LAMBDA(a,COUNTA(FILTER(m,m=a))))))

静听溪流

=SUM(N(LOOKUP(ROW($1:$27),IF($B$3:$B$29<>"",ROW($1:$27)),$B$3:$B$29)=B33))

橒♂蝣

=LET(x,XLOOKUP(SEQUENCE(ROWS($B$3:$B$29)),SEQUENCE(ROWS($B$3:$B$29))/(XMATCH($B$3:$B$29,$B$3:$B$29)<>3),$B$3:$B$29,,-1), SUMPRODUCT(1*(x=B33)))

jivy1212

1. =LOOKUP(1,SUBTOTAL(3,OFFSET($B$1,MATCH(B33,$B$1:$B$29,)-1,{0,1},ROW($1:$16))))

复制代码