不规则的合并单元格情况下，怎么用公式统计对应的个数

yuanfen201 · 发表于 2024-5-25 11:05

本帖最后由 yuanfen201 于 2024-5-25 11:15 编辑

edwin11891 · 发表于 2024-5-25 11:06

上附件，没附件会没人理。。。

yuanfen201 · 发表于 2024-5-25 11:17

edwin11891 发表于 2024-5-25 11:06
上附件，没附件会没人理。。。

上了

solextrade · 发表于 2024-5-25 11:23

=SUMPRODUCT(N(LOOKUP(ROW($2:$33),ROW($2:$33)/(B$2:B$33>0),B$2:B$33)&LOOKUP(ROW($2:$33),ROW($2:$33)/(C$2:C$33>0),C$2:C$33)=F2&G2))

edwin11891 · 发表于 2024-5-25 11:31

=LET(sA,SCAN("",B2:B33,LAMBDA(x,y,IF(y="",x,y))),sB,SCAN("",C2:C33,LAMBDA(x,y,IF(y="",x,y))),res,GROUPBY(HSTACK(HSTACK(MATCH(sA,sA,),sA),sB),D2:D33,COUNTA,,1),VSTACK({"产品名称","类别","物料名称数量"},DROP(res,,1)))

复制代码

mengrs · 发表于 2024-5-25 11:31

=SUMPRODUCT((LOOKUP(ROW($2:$33),ROW($2:$33)/(B$2:B$33<>""),B$2:B$33)=F2)*(LOOKUP(ROW($2:$33),ROW($2:$33)/(C$2:C$33<>""),C$2:C$33)=G2))

yuanfen201 · 发表于 2024-5-25 13:53

mengrs 发表于 2024-5-25 11:31
=SUMPRODUCT((LOOKUP(ROW($2:$33),ROW($2:$33)/(B$2:B$33""),B$2:B$33)=F2)*(LOOKUP(ROW($2:$33),ROW($2:$3 ...

强

yuanfen201 · 发表于 2024-5-25 13:54

solextrade 发表于 2024-5-25 11:23
=SUMPRODUCT(N(LOOKUP(ROW($2:$33),ROW($2:$33)/(B$2:B$33>0),B$2:B$33)&LOOKUP(ROW($2:$33),ROW($2:$33)/( ...

感谢

yuanfen201 · 发表于 2024-5-25 13:56

edwin11891 发表于 2024-5-25 11:31

hugeinfo · 发表于 2024-5-25 14:33

=LET(a,SCAN("",B2:B33,LAMBDA(x,y,IF(y="",x,y))),b,SCAN("",C2:C33,LAMBDA(x,y,IF(y="",x,y))),HSTACK(UNIQUE(HSTACK(a,b)), DROP(REDUCE("",UNIQUE(a&b),LAMBDA(x,y,VSTACK(x,COUNTA(FILTER(D2:D33,a&b=y) )))),1)))

		自动登录	找回密码
密码			免费注册

[求助] 不规则的合并单元格情况下，怎么用公式统计对应的个数