跨工作表多条件求和

pujizhongxue

汇总表.rar (16.56 KB, 下载次数: 5)

2023-6-1 15:54 上传

点击文件名下载附件
附件

我有一个工作簿，里面有五个工作表，分别是：第四周、第三周、第二周、第一周和总表，每个工作表的A列为学生姓名、B列为所在班级，C列为天数，我用下面的公式求出学生参加的总天数。=SUMPRODUCT((第四周!$A$2:$A$598=A2)*(第四周!$B$2:$B$598=B2)*(第四周!$C$2:$C$598))+SUMPRODUCT((第三周!$A$2:$A$602=A2)*(第三周!$B$2:$B$602=B2)*(第三周!$C$2:$C$602))+SUMPRODUCT((第二周!$A$2:$A$604=A2)*(第二周!$B$2:$B$604=B2)*(第二周!$C$2:$C$604))+SUMPRODUCT((第一周!$A$2:$A$1300=A2)*(第一周!$B$2:$B$1300=B2)*(第一周!$C$2:$C$1300))

但是，我觉得这个公式太过于繁琐了，求问大家能不能帮助简化一下公式。

jivy1212

1. =SUM(SUMIFS(INDIRECT({"第一","第二","第三","第四"}&"周!c:c"),INDIRECT({"第一","第二","第三","第四"}&"周!A:A"),A2,INDIRECT({"第一","第二","第三","第四"}&"周!b:b"),B2))

复制代码

博爱风雨同舟

=SUM(SUMIFS(INDIRECT(TEXT(ROW($1:$4),"第[DBNUM1]0周")&"!c:c"),INDIRECT(TEXT(ROW($1:$4),"第[DBNUM1]0周")&"!A:A"),A2,INDIRECT(TEXT(ROW($1:$4),"第[DBNUM1]0周")&"!b:b"),B2))

博爱风雨同舟

=LET(A,TEXT(ROW($1:$4),"第[DBNUM1]0周"),SUM(SUMIFS(INDIRECT(A&"!c:c"),INDIRECT(A&"!A:A"),A2,INDIRECT(A&"!b:b"),B2)))
365版本

jivy1212

博爱风雨同舟 发表于 2023-6-1 16:19
=SUM(SUMIFS(INDIRECT(TEXT(ROW($1:$4),"第[DBNUM1]0周")&"!c:c"),INDIRECT(TEXT(ROW($1:$4),"第[DBNUM1]0 ...

1. =TEXT({1,2,3,4},"[DBNUM1]第0周!!c:c")

复制代码

博爱风雨同舟

jivy1212 发表于 2023-6-1 16:32

=SUM(SUMIFS(INDIRECT(TEXT(ROW($1:$4),"[DBNUM1]第0周""!c:c""")),INDIRECT(TEXT(ROW($1:$4),"[DBNUM1]第0周""!A:A""")),A2,INDIRECT(TEXT(ROW($1:$4),"[DBNUM1]第0周""!b:b""")),B2))