SUMIFS满足条件合计多个数据

答案:2 悬赏:0 手机版

解决时间 2021-01-14 18:52

提问者网友：欲望失宠
2021-01-13 22:15

SUMIFS满足条件合计多个数据

最佳答案

五星知识达人网友：十年萤火照君眠
2021-01-13 22:46

用SUMIFS设计公式
E14：
=SUMIFS(Sheet2!AF:AF,Sheet2!W:W,">="&DATE(YEAR(B1),1,1),Sheet2!W:W,"<"&DATE(YEAR(B1)+1,1,1))+SUMIFS(Sheet2!AG:AG,Sheet2!W:W,">="&DATE(YEAR(B1),1,1),Sheet2!W:W,"<"&DATE(YEAR(B1)+1,1,1))+SUMIFS(Sheet2!AJ:AJ,Sheet2!W:W,">="&DATE(YEAR(B1),1,1),Sheet2!W:W,"<"&DATE(YEAR(B1)+1,1,1))+SUMIFS(Sheet2!AM:AM,Sheet2!W:W,">="&DATE(YEAR(B1),1,1),Sheet2!W:W,"<"&DATE(YEAR(B1)+1,1,1))+SUMIFS(Sheet2!AN:AN,Sheet2!W:W,">="&DATE(YEAR(B1),1,1),Sheet2!W:W,"<"&DATE(YEAR(B1)+1,1,1))
F14：
=SUMIFS(Sheet2!AF:AF,Sheet2!W:W,">="&DATE(YEAR(B1),F3,1),Sheet2!W:W,"<"&DATE(YEAR(B1),F3+1,1))+SUMIFS(Sheet2!AG:AG,Sheet2!W:W,">="&DATE(YEAR(B1),F3,1),Sheet2!W:W,"<"&DATE(YEAR(B1),F3+1,1))+SUMIFS(Sheet2!AJ:AJ,Sheet2!W:W,">="&DATE(YEAR(B1),F3,1),Sheet2!W:W,"<"&DATE(YEAR(B1),F3+1,1))+SUMIFS(Sheet2!AM:AM,Sheet2!W:W,">="&DATE(YEAR(B1),F3,1),Sheet2!W:W,"<"&DATE(YEAR(B1),F3+1,1))+SUMIFS(Sheet2!AN:AN,Sheet2!W:W,">="&DATE(YEAR(B1),F3,1),Sheet2!W:W,"<"&DATE(YEAR(B1),F3+1,1))

全部回答

1楼网友：忘川信使
2021-01-14 00:07

E14公式=SUMPRODUCT((YEAR(Sheet2!W4:W10000)=YEAR(B1))*(Sheet2!AF4:AF10000+Sheet2!AG4:AG10000+Sheet2!AM4:AM10000+Sheet2!AN4:AN10000))
F14公式=SUMPRODUCT(((YEAR(Sheet2!W4:W10000)=YEAR(B1))*(MONTH(Sheet2!W4:W10000)=F3))*(Sheet2!AF4:AF10000+Sheet2!AG4:AG10000+Sheet2!AM4:AM10000+Sheet2!AN4:AN10000))追问结果是#VALUE！的，方便我把表格发你解决吗追答W4:W10000计算区域必需是数字，不能使文本等

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息