开贴--[字符争霸第18期]求每月营业额

gdpgdp317

=CHOOSE(COLUMN()-4,TEXT(41225+ROW()*30,"yyyy年m月"),SUM($G$2:$G2),SUM(IFERROR((TEXT($B:$B,"yyyy年m月")=$E2)*$C:$C,0)))

113字符，也不知道符合要求不，需要迭代2次

manageadmi

我的解答如下

滴答滴

88

1. =CHOOSE(COLUMN()-4,TEXT(EDATE(41244,A2),"e年m月"),SUMIF(A:A,"<"&EDATE(D2,1),B:B),D2-N(D1))

复制代码

chunlin1wang

字符多了点

1. =IF(COLUMN()<6,TEXT(DATE(2013,ROW()-1,1),"e年m月"),SUMIFS(C:C,B:B,"<"&DATE(2013,ROW(),1),B:B,">"&DATE(2013,ROW()-1,)*(COLUMN()=7)))

复制代码

1. =IF({1,0,0},TEXT(41214+ROW()*31,"e年m月"),MMULT(SUMIF(B:B,"<"&DATE(2013,ROW()-{0,1},1),C:C),{0,1,1;0,0,-1}))

复制代码

天地有雪

E2:G2三键下拉，126字符，高版本函数

1. =IF({1,0,0},TEXT(DATE(2013,ROW(A1),1),"e年m月"),SUMIFS(C:C,B:B,"<="&DATE(2013,ROW(A2),0),B:B,">="&DATE(2013,ROW(A1)^{0,0,1},1)))

复制代码

xinxin8201

=IF(D2="",TEXT(41230+ROW()*30,"yyyy年m月"),SUMIF($B:$B,"<"&$E3,$C:$C)+SUM($C:$C)*(E3="")-N(D1))

快乐小小

1. =IF(COLUMN()=5,TEXT(DATE(YEAR($B2),MONTH($B2)+ROW($B1)-1,1),"yyyy年m月"),SUMIF($B:$B,"<="&TEXT(DATE(YEAR($B2),MONTH($B2)+ROW($B1),0),"yyyy-mm-dd"),$C:$C)-IF(COLUMN()=6,0,SUMIF($B:$B,"<="&TEXT(DATE(YEAR($B2),MONTH($B2)+ROW($B1)-1,),"yyyy-mm-dd"),$C:$C)))

复制代码

路边的记忆

E2:G2输入公式下拉

1. =CHOOSE({1,2,3},TEXT(41259+31*A2,"e年m月"),SUM(C:C)-SUMIF(B:B,">="&E3,C:C),F2-N(F1))

复制代码

cleverzhzhf

先来一个不用迭代的，155字符：

1. =CHOOSE(COLUMN()-4,TEXT(DATE(2013,ROW(),),"e年m月"),SUMIF(B:B,"<="&DATE(2013,ROW(),),C:C),SUMIFS(C:C,B:B,"<="&DATE(2013,ROW(),),B:B,">"&DATE(2013,ROW()-1,)))

复制代码

启用迭代，94字符：

1. =CHOOSE({1,2,3},TEXT(DATE(2013,ROW(),),"e年m月"),SUMIF(B:B,"<="&DATE(2013,ROW(),),C:C),F2-N(F1))

复制代码

再搞个不符合规则的，设置E列单元格格式为yyyy年m月，启用迭代，66字符：

1. =CHOOSE({1,2,3},DATE(2013,ROW(),),SUMIF(B:B,"<="&E2,C:C),F2-N(F1))

复制代码

Traum

1. =IFERROR(IF(D2,D2-N(D1),TEXT(B2+(ROW()-2)*30,"e年m月")),SUMIF(A:A,"<="&EOMONTH(D2,0),B:B))

复制代码

贴着。