求助！

那你你所

只用一个公式，汇总四大行每月收到的货款和支出的材料

求助.rar (10.05 KB, 下载次数: 114)

2021-9-20 13:02 上传

点击文件名下载附件
求助.rar

浪子逐云

抛砖引玉一下。。。
=SUM(SUMIFS(IF($B3="货款",INDIRECT({"农业银行","中国银行","建设银行","工商银行"}&"!$C:$C"),INDIRECT({"农业银行","中国银行","建设银行","工商银行"}&"!$D:$D")),INDIRECT({"农业银行","中国银行","建设银行","工商银行"}&"!$B:$B"),"<="&-LOOKUP(,-(LEFTB(C$2,2)&"-"&ROW($28:31))),INDIRECT({"农业银行","中国银行","建设银行","工商银行"}&"!$E:$E"),$B3))-N(B3)

黑粉

差不多
=SUM(SUMIFS(INDIRECT({"农业";"中国";"建设";"工商"}&"银行!"&{"C:C","D:D"}),INDIRECT({"农业";"中国";"建设";"工商"}&"银行!E:E"),$B3,INDIRECT({"农业";"中国";"建设";"工商"}&"银行!B:B"),">"&DATE(2021,LEFTB(C$2,2),),INDIRECT({"农业";"中国";"建设";"工商"}&"银行!B:B"),"<"&DATE(2021,LEFTB(C$2,2)+1,1)))

西岗山宅男

大佬不来，我自己给自己优化一下吧！本质没变，还是啰嗦。。。
=SUM(SUMIFS(INDIRECT({"农业","中国","建设","工商"}&"银行!"&IF($B3="货款","C:C","D:D")),INDIRECT({"农业","中国","建设","工商"}&"银行!B:B"),"<="&-LOOKUP(,-(LEFTB(C$2,2)&"-"&ROW($28:31))),INDIRECT({"农业","中国","建设","工商"}&"银行!E:E"),$B3))-SUM($B3:B3)

陈怡然

借用楼上公式，如果是365版本，公式简单多了
=LET(行,{"农业";"中国";"建设";"工商"}&"银行!",SUM(SUMIFS(INDIRECT(行&{"C:C","D:D"}),INDIRECT(行&"E:E"),$B3,INDIRECT(行&"B:B"),">"&DATE(2021,LEFTB(C$2,2),),INDIRECT(行&"B:B"),"<"&DATE(2021,LEFTB(C$2,2)+1,1))))

刘老三烤肉小海鲜

=SUM(SUMIFS(INDIRECT({"农业银行","中国银行","建设银行","工商银行"}&"!C"&ROW(),),INDIRECT({"农业银行","中国银行","建设银行","工商银行"}&"!$B:$B"),"<"&DATE(2021,1+(0&LEFT(TRANSPOSE(B$2:C$2),1)),1),INDIRECT({"农业银行","中国银行","建设银行","工商银行"}&"!$E:$E"),$B3)*{-1;1})