根据日期求和

丫丫0574 · 发表于 2024-7-20 10:53

浮生若梦~~~ · 发表于 2024-7-20 12:54

=SUMPRODUCT((MONTH(IF(DAY(A$6:A$285)>25,A$6:A$285+8,A$6:A$285))&"月"=N2)*F$6:F$285)

良良 · 发表于 2024-7-20 12:56

Q3中红字需改

O2=NPV(-2,SUMIF(A:A,"<"&EDATE(SUBSTITUTE(LEFT(Q2,8),".","-"),{0,1}),F:F))

沈默00 · 发表于 2024-7-20 13:20

=LET(a,SUBSTITUTE(Q2,".","/"),SUMIFS(F:F,A:A,">="&TEXT(TEXTBEFORE(a,"-"),"e/m/d"),A:A,"<="&TEXT(TEXTAFTER(a,"-"),"e/m/d"))) 下拉

jisijie630 · 发表于 2024-7-20 13:51

O2:

=SUMIF(A:A,"<"&(2024&"/"&LEFT(N2,LEN(N2)-1)&"/26")/1,F:F)-SUM(O$1:O1)

ww87725244 · 发表于 2024-7-20 14:30

=SUMIF($A$6:$A$285,"<="&DATE(2024,--SUBSTITUTE(N2,"月",""),25),$F$6:$F$285)-SUM($O$1:O1)

我的城市你来过 · 发表于 2024-7-20 15:52

=SUM(IF((TEXT($A$6:$A$285,"emmdd")>=TEXT(SUBSTITUTE(20&REGEXP(Q2,"[0-9.]+(?=-)"),".","/"),"emmdd"))*((TEXT($A$6:$A$285,"emmdd")<TEXT(SUBSTITUTE(20&REGEXP(Q2,"(?<=-)[0-9.]+"),".","/"),"emmdd"))),$F$6:$F$285,0))

飞天篮球猪 · 发表于 2024-7-21 08:47

世界客都 · 发表于 2024-7-21 17:48

我提供2种简单的方法：

=SUMIFS(F:F,A:A,">="&EDATE(45286,ROW()-2),A:A,"<="&EDATE(45316,ROW()-2))

复制代码

=SUM((EOMONTH(ABS(A$6:A999-25),)=EDATE(45291,ROW()-2))*F$6:F999)

复制代码

下拉，第二个公式是数组公式，需要按ctrl+shift+enter

		自动登录	找回密码
密码			免费注册

[求助] 根据日期求和

评分

评分

评分

评分