如何根据动态条件求和

干勇平

求助各位，根据附件表P8的动态科目，求对应班级的分数值

其中语文成绩是阅读成绩-书写扣分-作文扣分。数学是数学成绩+附加题分数，详见附件。
求P列公式，不添加原表的辅助列，如何用公式啊。

solextrade

=SUMIF(C:C,O10,INDEX(E:K,,MATCH(P$8,E$8:K$8,)))

无关岁月

1. =CHOOSE(MATCH(P$8,{"语文","数学","英语"},),SUM(SUMIF(C:C,O10,OFFSET(C:C,,{3,6,7}))*{1,-1,-1}),SUM(SUMIF(C:C,O10,OFFSET(C:C,,{2,8}))),SUMIF(C:C,O10,G:G))

复制代码

博爱风雨同舟

=LET(A,($C$9:$C$24=O10),SWITCH(TRUE,$P$8="语文",SUM(($F$9:$F$24-MMULT($I$9:$J$24,{1;1}))*A),$P$8="数学",SUMPRODUCT(($E$9:$E$24+$K$9:$K$24)*A),SUMPRODUCT(($G$9:$G$24)*A)))

qydn

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

菜狗一只

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

复制代码

EXCEL版本比较低的话可以把filter换成sumif

quqiyuan

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

复制代码

我的城市你来过

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

我的城市你来过

=SUM(IFERROR(CHOOSE(MAX(($P$8={"数字","语文","英语"})*{1,2,3}),$E$9:$E$24+$K$9:$K$24,$F$9:$F$24-$I$9:$I$24-$J$9:$J$24,$G$9:$G$24)*IF($C$9:$C$24=$O10,ROW($C$9:$C$24)^0,""),""))

干勇平

菜狗一只 发表于 2024-3-8 10:58
EXCEL版本比较低的话可以把filter换成sumif

谢谢啊，我试下，换成SUMIF,咋算不了...