如何根据动态条件求和

回吧天堂

quqiyuan 发表于 2024-3-8 11:03

其实问题挺简单，往往是想复杂了，结果用上mmult offset等函数。而这个公式只用了sumproduct和if两个函数。

风扇Facha

IF(P$8="数学",SUMIF(C$9:C$24,O10,E$9:E$24)+SUMIF(C$9:C$24,O10,K$9:K$24),IF(P$8="英语",SUMIF(C$9:C$24,O10,G$9:G$24),SUMIF(C$9:C$24,O10,F$9:F$24)-SUMIF(C$9:C$24,O10,I$9:I$24)-SUMIF(C$9:C$24,O10,J$9:J$24)))

Holiday2022

P10=SUM(IF($C$9:$C$24=$O10,SWITCH(P$8,"数学",$E$9:$E$24+$K$9:$K$24,"语文",$F$9:$F$24-$I$9:$I$24-$J$9:$J$24,$G$9:$G$24),""))
公式下拉

Holiday2022

SWITCH函数相当于IFS函数，但比IFS更加简洁

mykitami

=SUM(($C$9:$C$24=O10)*(IFS($P$8="数学",$E$9:$E$24+$K$9:$K$24,$P$8="语文",$F$9:$F$24-$I$9:$I$24-$J$9:$J$24,1,$G$9:$G$24)))

知夏l

方法一：LET(x,IF({1,0,1,0,0,0,0},$E$8:$K$8,{"数学","语文","英语","总分","语文","语文","数学"}),y,IF({1,1,1,1,0,0,1},$E$9:$K$24,-$E$9:$K$24),z,VSTACK(x,y),SUM(FILTER(FILTER(z,x=$P$8),$C$8:$C$24=O10)))

方法二：LET(x,GROUPBY(C9:C24,HSTACK(E9:E24+K9:K24,F9:F24-I9:I24-J9:J24,G9:G24),SUM,0,0),IFS(P8="数学",CHOOSECOLS(x,2),P8="语文",CHOOSECOLS(x,3),P8="英语",CHOOSECOLS(x,4)))

gaozengxun

=SUM(SUMIF(C:C,$O10,OFFSET(C:C,,SWITCH($P$8,"数学",{2,8,9},"语文",{3,6,7},{4,9,9})))*SWITCH($P$8,"语文",{1,-1,-1},{1,1,1}))