提取名字，再按名字汇总金额

丫丫0574

提取名字，再按名字汇总金额

liujing_hotwind

=IFERROR(SUBSTITUTE(INDEX($B$1:$B$20,SMALL(IF((LEFT($B$2:$B$20,3)="联系人")*ROW($B$2:$B$20)=0,4^8,(LEFT($B$2:$B$20,3)="联系人")*ROW($B$2:$B$20)),ROW(A1))),"联系人:",""),"")
=SUM(OFFSET(INDIRECT("b"&MATCH(H3,SUBSTITUTE($B$1:$B$20,"联系人:",""),)),2,4,IF(H4="",99,MATCH(H4,SUBSTITUTE($B$1:$B$20,"联系人:",""),))-MATCH(H3,SUBSTITUTE($B$1:$B$20,"联系人:",""),),))

满坛皆为吾师

H3站桩

1. =MID(FILTER(B2:B99,LEFT(B2:B99,3)="联系人"),5,6)

复制代码

I3下拉

1. =SUM(INDIRECT(TEXTJOIN(":",,TEXT(MATCH("*"&H3:H4,B:B,)-{0;1},"F0;;F99"))))

复制代码

bbm000000

=SUM(IFNA((LOOKUP(ROW($B$1:$B$20),IF(LEFT($B$1:$B$20,3)="联系人",ROW($B$1:$B$20)),MID($B$1:$B$20,5,9))=H3)*N(+$F$1:$F$20),0))数组

Tony412

求和
=SUM(IFERROR((LOOKUP(ROW($B$2:$B$20),ROW($B$2:$B$20)/(LEFT($B$2:$B$20,3)="联系人"),IF(ROW($B$2:$B$20)/(LEFT($B$2:$B$20,3)="联系人"),SUBSTITUTE($B$2:$B$20,"联系人:","")))=H3)*($F$2:$F$20),0))

逐渐陌生

博爱风雨同舟

=TOCOL(TEXTAFTER(B2:B99,":"),3)
=SUMPRODUCT(IFERROR((SCAN("",TEXTAFTER($B$2:$B$99,":"),LAMBDA(X,Y,IF(ISNA(Y),X,Y)))=H3)*(F2:F99),))
365版本

mykitami

H3公式：

=LET(b,$B$1:$B$20,
           m,SCAN("",b,LAMBDA(x,y,IF(LEFT(y,4)="联系人:",MID(y,5,2),x))),
             UNIQUE(DROP(m,1),,0)
         )



I3公式：



=LET(b,$B$1:$B$20,
           f,$F$1:$F$20,
          m,SCAN("",b,LAMBDA(x,y,IF(LEFT(y,4)="联系人:",MID(y,5,2),x))),
               IFERROR(SUM(TAKE(FILTER(HSTACK(m,f),m=H3),,-1)),"")
          )

ericxzhou

丫丫0574

ericxzhou 发表于 2023-7-26 12:40

你好，有压缩包么