多条件求和

dongfangxielong · 发表于 2024-5-23 09:58

624134745 · 发表于 2024-5-23 10:11

=SUM((A2:A15=F2)*(C2:C15)*(ROW(B2:B15)<=MAX((B2:B15=G2)*ROW(B2:B15))))仅针对附件水1个

mykitami · 发表于 2024-5-23 10:12

=LET(a,VSTACK(TEXT(SEQUENCE(6),"[DBnum1]"),"日"),b,MATCH(RIGHT(B2:B15),a,),SUM(FILTER(C2:C15,(b<=MATCH(RIGHT(G2),a,))*(A2:A15=F2))))

leepoison · 发表于 2024-5-23 10:13

=LET(x,FILTER(B2:C15,A2:A15=F2),y,INDEX(x,,2),z,MATCH(G2,INDEX(x,,1)),m,SEQUENCE(z),n,SUM(INDEX(y,m)),n)

624134745 · 发表于 2024-5-23 10:15

=SUM((A2:A15=F2)*(IFERROR(FIND(RIGHT(B2:B15),"一二三四五六日"),)<=FIND(RIGHT(G2),"一二三四五六日"))*C2:C15)不偷鸡的解法

mengrs · 发表于 2024-5-23 10:21

=SUMPRODUCT((A$2:A$15=F2)*(LOOKUP(B$2:B$15,TEXT(ROW($1:$7),"星期[dbnum1]0"),ROW($1:$7))<=LOOKUP(G2,TEXT(ROW($1:$7),"星期[dbnum1]0"),ROW($1:$7)))*C$2:C$15)

hugeinfo · 发表于 2024-5-23 10:26

=SUMPRODUCT((TOCOL(IF(B2:B15={"星期一","星期二","星期三","星期四","星期五","星期六","星期日"},COLUMN(A:G),x),3,FALSE)<=MATCH(G2,{"星期一","星期二","星期三","星期四","星期五","星期六","星期日"}))*($A$2:$A$15=F2)*$C$2:$C$15)

逐渐陌生 · 发表于 2024-5-23 10:37

hxj119 · 发表于 2024-5-23 10:43

=LET(a,LOOKUP(1,0/($B$2:$B$15=G2),ROW($B$2:$B$15)-1),SUM((OFFSET($A$1,1,,a)=F2)*(OFFSET($A$1,1,2,a))))

象山海鲜 · 发表于 2024-5-23 11:36

=SUM(SUMIFS(C:C,A:A,F2,B:B,"星期"&MID("一二三四五六日",ROW(1:7),ROW(1:7)<=FIND(RIGHT(G2),"一二三四五六日"))))

复制代码

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

[求助] 多条件求和

评分