[已解决]急需求助，请大神不吝赐教，先谢啦！

kingve52

麻烦老师们帮忙设置一下附件里的公式，统计条件也在附件里面，谢谢！

最佳答案

月排行榜 / 总排行榜

心正意诚身修

2016-2-17 15:29

=SUMPRODUCT(($F$2:$F$1384=$K10)*($E$2:$E$1384="BB")*(($A$2:$A$1384<=$K$5)*($A$2:$A$1384>=LEFT($K$5,6)*100+1))*($C$2:$C$1384))
件數
=SUMPRODUCT(($F$2:$F$1384=$K10)*($E$2:$E$1384="BB")*(($A$2:$A$1384<=$K$5)*($A$2:$A$1384>=LEFT($K$5,6)*100+1))*($B$2:$B$1384))
金額

跳转到最佳答案楼层

心正意诚身修

=SUMPRODUCT(($F$2:$F$1384=$K10)*($E$2:$E$1384="BB")*(($A$2:$A$1384<=$K$5)*($A$2:$A$1384>=LEFT($K$5,6)*100+1))*($C$2:$C$1384))
件數
=SUMPRODUCT(($F$2:$F$1384=$K10)*($E$2:$E$1384="BB")*(($A$2:$A$1384<=$K$5)*($A$2:$A$1384>=LEFT($K$5,6)*100+1))*($B$2:$B$1384))
金額

wqjyyylkz

L10=SUMIFS(C$2:C$1384,C$2:C$1384,"1",E$2:E$1384,"BB",F$2:F$1384,"="&K10)
M10=SUMIFS(B$2:B$1384,C$2:C$1384,"1",E$2:E$1384,"BB",F$2:F$1384,"="&K10)
向下填充公式即可