excel多列单条件求和公式简化=SUMIF(INDIRECT("'Sheet1("&COLUMN(B1)&")'!$S:$S"),Sheet1!$A2,INDIRECT("'Sheet1("&COLUMN(B1)&")'!$J:$J"))+SUMIF(INDIRECT("'Sheet1("&COLUMN(B1)&")'!$T:$T"),Sheet1!$A2,INDIRECT("'Sheet1("&COLUMN(B1)&")'!$J:$J"))

Question

excel多列单条件求和公式简化
=SUMIF(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$S:$S"),Sheet1!$A2,INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$J:$J"))+SUMIF(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$T:$T"),Sheet1!$A2,INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$J:$J"))+SUMIF(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$U:$U"),Sheet1!$A2,INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$J:$J"))+SUMIF(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$V:$V"),Sheet1!$A2,INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$J:$J"))+SUMIF(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$W:$W"),Sheet1!$A2,INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$J:$J"))+SUMIF(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$X:$X"),Sheet1!$A2,INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$J:$J"))+SUMIF(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$Y:$Y"),Sheet1!$A2,INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$J:$J"))
只是其中的一部分,实在太长了,连问题补充里都写不满,不过格式都是iyang的,excel提示太多函数了...求高手简化下啊

Answer 1

干吗要弄成sheet1(2)这样啊?
还要拖动公式的么?
不知道你目的,你所贴公式可以变成这样
=sumproduct((INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$S:$S")=Sheet1!$A2+(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$T:$T")=Sheet1!$A2+(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$U:$U")=Sheet1!$A2+(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$V:$V")=Sheet1!$A2+(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$W:$W")=Sheet1!$A2+(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$X:$X")=Sheet1!$A2+(INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$Y:$Y")=Sheet1!$A2)*INDIRECT("'Sheet1 ("&COLUMN(B1)&")'!$J:$J")
)