那你你所 发表于 2021-9-20 13:02:00

求助！

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

浪子逐云 发表于 2021-10-19 20:09:11

抛砖引玉一下。。。
=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)

黑粉 发表于 2021-11-4 07:18:55

差不多
=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)))

西岗山宅男 发表于 2021-11-17 03:53:46

大佬不来，我自己给自己优化一下吧！本质没变，还是啰嗦。。。
=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)

陈怡然 发表于 2021-11-22 08:00:11

借用楼上公式，如果是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))))

刘老三烤肉小海鲜 发表于 2021-12-4 14:11:42

=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})
页: [1]
查看完整版本: 求助！