择年份后，按月份求和

yclyb

根据M1单元格选择年份，然后按月份求和。没有数据时，单元格不显示0。 按月份求和.zip (14.69 KB, 下载次数: 20)

2024-12-22 19:56 上传

点击文件名下载附件

zwzc

=IF(SUM($J$4:$J$115*(TEXT($B$4:$B$115,"e/mm")=TEXT($M$1&M$2,"e/mm")))=0,"",SUM($J$4:$J$115*(TEXT($B$4:$B$115,"e/mm")=TEXT($M$1&M$2,"e/mm"))))

序号10写成2025年了，数据有错误

无关岁月

不显示零的话，就要变成文本，感觉没必要

1. =NPV(-2,SUMIF($B:$B,"<="&EOMONTH($M$1&M$2,{-1,0}),$F:$F))

复制代码

leepoison

=LET(a,MAP(M2:X2,LAMBDA(x,IFERROR(SUM(FILTER(J4:J115,(YEAR(B4:B115)=1*REGEXP(M1,"\d+"))*(MONTH(B4:B115)=1*REGEXP(x,"\d+")))),""))),HSTACK(a,SUM(a)))

搞了半天第10行是2025，数据不细心，亲人两行泪

1007890405

=TEXT(SUM(IFERROR((YEAR($B4:$B123)=LEFT($M$1,4)*1)*(MONTH($B4:$B123)&"月"=M2)*$J4:$J123,0)),"0;0;;")

你是那块小饼干

1. =IFERROR(SUMPRODUCT((YEAR($B$4:$B$115)=LEFT($M$1,4)*1)*(MONTH($B$4:$B$115)=LEFTB(M2,2)*1),$J$4:$J$115),"")

复制代码

忙到飞起

=SCAN(1,COLUMN(A:L),LAMBDA(x,y,LET(a,CHOOSECOLS(FILTER(B4:J123,YEAR(B4:B123)=LEFT(M1,4)*1),1,-1),IFERROR(SUM(FILTER(TAKE(a,,-1),TEXT(TAKE(a,,1),"m")*1=y)),""))))

lanjian229

1. =SUM(IFERROR($J:$J*(TEXT($B:$B,"yyy年m月")=$M$1&M2),))

复制代码

=SUMPRODUCT($J:$J,N(TEXT($B:$B,"yyy年m月")=$M$1&M2))

袁勇

=TEXT(SUM(SUMIF($B:$B,"<="&EOMONTH($M$1&M$2,{-1,0}),$F:$F)*{-1,1}),"g/通用格式;;")

合计公式改为：=SUMPRODUCT((0&M3:X3)*1)

yclyb

谢谢大师们回帖，经测试，3楼、6楼、9楼可行，可能版本问题，其它楼测试不成功，同时也谢谢2楼及4楼指出了我输入的错误。