求助，多条件求和

caihoney

求助大神：目标：根据设备编号统计对应案件以及对应实验项目的设备使用时间。

袁勇

1. =SUMPRODUCT(TEXT(SUBSTITUTE(OFFSET(C$2:R$2,MATCH(B12,B$3:B$4,),),C12&"-"&D12&"：",""),"0;;;!0")*1)

复制代码

huaichuan1

前面的条件要自己输入。后面能求和

1. =SUMPRODUCT(($B$3:$B$4=B12)*(LEFT($C$3:$R$4,FIND("-",$C$3:$R$4)-1)=C12)*--IFERROR(RIGHT($C$3:$R$4,LEN($C$3:$R$4)-FIND(D12&"：",$C$3:$R$4)-2),0))

复制代码

hxj119

=SUM(IF(LEFT(C3:R3,FIND("：",C3:R3)-1)=C12&"-"&D12,RIGHT(C3:R3,2)/1))

part98

1. =LET(s,DROP(REDUCE("",B3:B4,LAMBDA(x,y,REDUCE(x,OFFSET(y,,ROW(1:16)),LAMBDA(m,n,VSTACK(m,HSTACK(y,TEXTSPLIT(n,{"-","："},,1))))))),1),VSTACK({"设备编号","案件编号","实现项目","使用时间"},GROUPBY(TAKE(s,,3),--TAKE(s,,-1),SUM,0,0)))

复制代码

ww87725244

=SUM(IFERROR(--SUBSTITUTE(OFFSET($B$2,MATCH(B12,$B$2:$B$4,)-1,1,,16),C12&"-"&D12&"：",""),0))

jisijie630

=SUMPRODUCT(IFERROR((B$3:B$4=B12)*FIND(C12&"-"&D12,C$3:R$4)*RIGHT(C$3:R$4,2),))

caihoney

袁勇 发表于 2024-6-27 09:58

感谢，大神，我现在的问题是只有上面的设备工时的统计表，下面的汇总表是没有的，是否有方法可以将上面表中的案件编号，实验项目提取出来呢？

jisijie630

ww87725244 发表于 2024-6-27 10:15
=SUM(IFERROR(--SUBSTITUTE(OFFSET($B$2,MATCH(B12,$B$2:$B$4,)-1,1,,16),C12&"-"&D12&"：",""),0))

学习6楼老师的：

=SUMPRODUCT(IFERROR((B$3:B$4=B12)*SUBSTITUTE(C$3:R$4,C12&"-"&D12&"：",),))

lws

1. =REDUCE({"设备编号","案件编号","实现项目","使用时间"},B3:B4,LAMBDA(X,Y,LET(S,TEXTSPLIT(TEXTJOIN("+",,OFFSET(Y,,1,,16)),{"-","："},"+"),VSTACK(X,IFNA(HSTACK(Y,GROUPBY(TAKE(S,,2),--TAKE(S,,-1),SUM,,0)),Y)))))

复制代码