条件求和开票金额汇总，求简单优化公式

yangbin5670

条件求和开票金额汇总，求简单优化公式

yangbin5670

=SUMIFS([单位往来台账.xlsx]销项往来!$T:$T,[单位往来台账.xlsx]销项往来!$S:$S,">="&DATE($H$1,$J$1,1),[单位往来台账.xlsx]销项往来!$B:$B,$M4)+SUMIFS([单位往来台账.xlsx]销项往来!$X:$X,[单位往来台账.xlsx]销项往来!$W:$W,">="&DATE($H$1,$J$1,1),[单位往来台账.xlsx]销项往来!$B:$B,$M4)+SUMIFS([单位往来台账.xlsx]销项往来!$AB:$AB,[单位往来台账.xlsx]销项往来!$AA:$AA,">="&DATE($H$1,$J$1,1),[单位往来台账.xlsx]销项往来!$B:$B,$M4)+SUMIFS([单位往来台账.xlsx]销项往来!$AF:$AF,[单位往来台账.xlsx]销项往来!$AE:$AE,">="&DATE($H$1,$J$1,1),[单位往来台账.xlsx]销项往来!$B:$B,$M4)+SUMIFS([单位往来台账.xlsx]销项往来!$AJ:$AJ,[单位往来台账.xlsx]销项往来!$AI:$AI,">="&DATE($H$1,$J$1,1),[单位往来台账.xlsx]销项往来!$B:$B,$M4)+SUMIFS([单位往来台账.xlsx]销项往来!$AN:$AN,[单位往来台账.xlsx]销项往来!$AM:$AM,">="&DATE($H$1,$J$1,1),[单位往来台账.xlsx]销项往来!$B:$B,$M4)+SUMIFS([单位往来台账.xlsx]销项往来!$AR:$AR,[单位往来台账.xlsx]销项往来!$AQ:$AQ,">="&DATE($H$1,$J$1,1),[单位往来台账.xlsx]销项往来!$B:$B,$M4)+SUMIFS([单位往来台账.xlsx]销项往来!$AV:$AV,[单位往来台账.xlsx]销项往来!$AU:$AU,">="&DATE($H$1,$J$1,1),[单位往来台账.xlsx]销项往来!$B:$B,$M4)+SUMIFS([单位往来台账.xlsx]销项往来!$AZ:$AZ,[单位往来台账.xlsx]销项往来!$AY:$AY,">="&DATE($H$1,$J$1,1),[单位往来台账.xlsx]销项往来!$B:$B,$M4)+SUMIFS([单位往来台账.xlsx]销项往来!$BD:$BD,[单位往来台账.xlsx]销项往来!$BC:$BC,">="&DATE($H$1,$J$1,1),[单位往来台账.xlsx]销项往来!$B:$B,$M4)，这个公式结果正确，嫌公式太长了，求简单优化公式

良良

B5=SUMPRODUCT(([开票金额条件求和20240719.xlsx]销项往来!B$4:B$25=A5)*(LEFT([开票金额条件求和20240719.xlsx]销项往来!I$3:AS$3,4)="开票日期")*([开票金额条件求和20240719.xlsx]销项往来!I$4:AS$25>=DATE(B$1,D$1,1))*[开票金额条件求和20240719.xlsx]销项往来!J$4:AT$25)

yangbin5670

麻烦高手帮忙解决，谢谢哈！

yangbin5670

良良 发表于 2024-7-19 12:08
B5=SUMPRODUCT(([开票金额条件求和20240719.xlsx]销项往来!B$4:B$25=A5)*(LEFT([开票金额条件求和20240719. ...

老师太厉害了，完美的解决了我的问题，谢谢！