求和

杨杨1991315

这个是辅助列，我想不借助辅助列，通过输入简称，比如说“大禹”“绘画”直接求出标记的值，表格的行数不是固定的，会增加或减少，后面的“汇总”表格里的统计，也是借助了辅助列

无聊的黑猫

=SUM(IFERROR(N(INDIRECT("'2306'!BL"&IF('2306'!$B$3:$Q$18=B4,ROW('2306'!$B$3:$Q$18)))),0))
=SUM(IFERROR(N(INDIRECT("'2306'!BM"&IF('2306'!$B$3:$Q$18=B4,ROW('2306'!$B$3:$Q$18))))*N(INDIRECT("'2306'!"&TEXT(IF('2306'!$B$3:$Q$18=B4,(ROW('2306'!$B$3:$Q$18)-1)*100+COLUMN('2306'!$B$3:$Q$18)),"r00c00"),))+(N(INDIRECT("'2306'!BM"&IF('2306'!$B$3:$Q$18=B4,ROW('2306'!$B$3:$Q$18))))=0)*N(INDIRECT("'2306'!BL"&IF('2306'!$B$3:$Q$18=B4,ROW('2306'!$B$3:$Q$18))))*N(INDIRECT("'2306'!"&TEXT(IF('2306'!$B$3:$Q$18=B4,(ROW('2306'!$B$3:$Q$18)-1)*100+COLUMN('2306'!$B$3:$Q$18)),"r00c00"),)),0))
=SUM(IFERROR(N(INDIRECT("'2306'!BM"&IF('2306'!$B$3:$Q$18=B4,ROW('2306'!$B$3:$Q$18))))*N(INDIRECT("'2306'!"&TEXT(IF('2306'!$B$3:$Q$18=B4,(ROW('2306'!$B$3:$Q$18)-1)*100+COLUMN('2306'!$B$3:$Q$18)+1),"r00c00"),))+(N(INDIRECT("'2306'!BM"&IF('2306'!$B$3:$Q$18=B4,ROW('2306'!$B$3:$Q$18))))=0)*N(INDIRECT("'2306'!BL"&IF('2306'!$B$3:$Q$18=B4,ROW('2306'!$B$3:$Q$18))))*N(INDIRECT("'2306'!"&TEXT(IF('2306'!$B$3:$Q$18=B4,(ROW('2306'!$B$3:$Q$18)-1)*100+COLUMN('2306'!$B$3:$Q$18)+1),"r00c00"),)),0))
从左到右 数组三键

博爱风雨同舟

F4=LET(A,('2306'!$B$3:$Q$18=B4)*ROW($5:$20)*100+COLUMN(D:S),SUM(N(INDIRECT("2306!"&TOCOL(TEXT(IF(A<100,0/0,A),"R0C00"),3),))))
365版本