Question

Excel中sumif的跨工作表求和问题

计算一年级汇总、二年级汇总、三年级汇总……六年级汇总共6个工作表的A列等于黄小，将一年级汇总、二年级汇总、三年级汇总……六年级汇总的E列数值相加求和。

Answer 1

一、=SUM(DSUM(INDIRECT({"一","二","三","四","五","六"}&"年级汇总"&"!a1:e500"),5,$A$1:$A2))

二、=SUM(SUMIF(INDIRECT({"一";"二";"三";"四";"五";"六"}&"年级汇总"&"!a:a"),"黄小",INDIRECT({"一";"二";"三";"四";"五";"六"}&"年级汇总"&"!e:e")))

三、=SUMPRODUCT(SUMIF(INDIRECT({"一","二","三","四","五","六"}&"年级汇总"&"!a:a"),A2,INDIRECT({"一","二","三","四","五","六"}&"年级汇总"&"!e:e")))

Answer 2

给个图，帮你看看

追问

意思和这个差不多，科我不会修改公式。
http://zhidao.baidu.com/link?url=5XwToB5JjCGFOjExHObyVlaKcyLapNbXbIhnqXX_UzEaHHS7WNvqtyq6BI3EGjpeCPLXoEmrKp2PJjJQuehZMq

追答

先将表名改为“1年级汇总”，大写一，改为数字1到6
=SUM(SUMIF(INDIRECT(ROW(1:6)&"年级汇总"&"!d:d"),"=黄小",INDIRECTROW(1:6)&"年级汇总"&"!d:d")))

追问

对不起，工作表标签不能改。

追答

=SUM(SUMIF(INDIRECT("一年级汇总"&"!A:A"),"黄小",INDIRECT("一年级汇总"&"!E:E")),SUMIF(INDIRECT("二年级汇总"&"!A:A"),"黄小",INDIRECT("二年级汇总"&"!E:E")),SUMIF(INDIRECT("三年级汇总"&"!A:A"),"黄小",INDIRECT("三年级汇总"&"!E:E")),SUMIF(INDIRECT("四年级汇总"&"!A:A"),"黄小",INDIRECT("四年级汇总"&"!E:E")),SUMIF(INDIRECT("五年级汇总"&"!A:A"),"黄小",INDIRECT("五年级汇总"&"!E:E")),SUMIF(INDIRECT("六年级汇总"&"!A:A"),"黄小",INDIRECT("六年级汇总"&"!E:E")))