[已解决]多条件查找与汇总

紫映竹

昨天脑子短路，怎么也弄不出来或者做不出来。我一开始想到的是把前场与后场的文字查找出来，想着用SUPRODUCT也能完成，再请教一下大家还没有其它的可以完成的？见附件。

最佳答案

月排行榜 / 总排行榜

hcm19522

2022-11-23 11:19

J2{=INDEX(D:D,SMALL(IF(MATCH(1+(ISERR(FIND("前场",C$2:C$6)))&D$2:D$6,1+(ISERR(FIND("前场",C$2:C$6)))&D$2:D$6,)=ROW($1:$5),ROW($2:$6),99),ROW(A1)))&""

K2{=IFERROR(LOOKUP(1,0/FIND({"前场","后场"},INDEX(C:C,SMALL(IF(MATCH(1+(ISERR(FIND("前场",C$2:C$6)))&D$2:D$6,1+(ISERR(FIND("前场",C$2:C$6)))&D$2:D$6,)=ROW($1:$5),ROW($2:$6)),ROW(A1)))),{"前场","后场"}),"")

L2=IF(J2="","",SUMPRODUCT(G$2:G$6*(D$2:D$6=J2)*ISNUMBER(FIND(K2,C$2:C$6))))

M2=IF(J2="","",SUMPRODUCT((D$2:D$6=J2)*ISNUMBER(FIND(K2,C$2:C$6))))

跳转到最佳答案楼层

紫映竹

为啥我上传不了附件，一定要压缩文件吗

风林火山

试试sumifs

紫映竹

风林火山 发表于 2022-11-22 16:38
试试sumifs

这也是一个可以尝试的。数据透视也行。还有啥好的办法

hcm19522

J2{=INDEX(D:D,SMALL(IF(MATCH(1+(ISERR(FIND("前场",C$2:C$6)))&D$2:D$6,1+(ISERR(FIND("前场",C$2:C$6)))&D$2:D$6,)=ROW($1:$5),ROW($2:$6),99),ROW(A1)))&""

K2{=IFERROR(LOOKUP(1,0/FIND({"前场","后场"},INDEX(C:C,SMALL(IF(MATCH(1+(ISERR(FIND("前场",C$2:C$6)))&D$2:D$6,1+(ISERR(FIND("前场",C$2:C$6)))&D$2:D$6,)=ROW($1:$5),ROW($2:$6)),ROW(A1)))),{"前场","后场"}),"")

L2=IF(J2="","",SUMPRODUCT(G$2:G$6*(D$2:D$6=J2)*ISNUMBER(FIND(K2,C$2:C$6))))

M2=IF(J2="","",SUMPRODUCT((D$2:D$6=J2)*ISNUMBER(FIND(K2,C$2:C$6))))

紫映竹

hcm19522 发表于 2022-11-23 11:19
J2{=INDEX(D:D,SMALL(IF(MATCH(1+(ISERR(FIND("前场",C$2:C$6)))&D$2:D$6,1+(ISERR(FIND("前场",C$2:C$6))) ...

非常感谢