求字符左侧的数据和

金色和谐家园 · 发表于 2023-12-8 23:25

各位老师，如何求出符合条件的字符左侧数据的和

万分感谢

工作簿2.rar (6.88 KB, 下载次数: 22)

静听溪流 · 发表于 2023-12-8 23:44

=SUBSTITUTE(SUBSTITUTE(IMSUM(IF(C8:C23="标准",SUBSTITUTE(0&D8:D23,"~","+")&"j",)),"+","~"),"j","")

静听溪流 · 发表于 2023-12-8 23:49

=SUBSTITUTE(SUBSTITUTE(IMSUM(IF((C8:C23="标准")*(LOOKUP(ROW(8:23),IF(B8:B23<>"",ROW(8:23)),B8:B23)="沈阳"),SUBSTITUTE(0&D8:D23,"~","+")&"j",)),"+","~"),"j","")

mykitami · 发表于 2023-12-9 00:16

=LET(a,FILTER(D8:D23,(C8:C23=C3)*(D8:D23<>"")),q,--TEXTBEFORE(a,"~"),h,--TEXTAFTER(a,"~"),SUM(q)&"~"&SUM(h))
=LET(c,SCAN(B8,B8:B23,LAMBDA(x,y,IF(y="",x,y))),a,FILTER(D8:D23,(C8:C23=C4)*(D8:D23<>"")*(c="沈阳")),q,--TEXTBEFORE(a,"~"),h,--TEXTAFTER(a,"~"),s,SUM(q)&"~"&SUM(h),s)

橒♂蝣 · 发表于 2023-12-9 09:19

=TEXTJOIN("-",1,MMULT(SEQUENCE(,ROWS(D8:D23))^0,IFERROR(--TEXTSPLIT(TEXTJOIN(";",1,IF(C8:C23=C3,D8:D23,0)),"~",";",1),0)))

橒♂蝣 · 发表于 2023-12-9 09:22

=TEXTJOIN("-",1,MMULT(SEQUENCE(,ROWS(D8:D23))^0,IFERROR(--TEXTSPLIT(TEXTJOIN(";",1,IF((C8:C23=C3)*(LOOKUP(ROW(B8:B23),ROW(B8:B23)/(B8:B23<>""),B8:B23)=B12),D8:D23,0)),"~",";",1),0)))

金色和谐家园 · 发表于 2023-12-9 15:41

各位老师，能不能实现分开求和，

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

[求助] 求字符左侧的数据和