求助大神一个关于条件求和的公式

areshsia

如何在BK6、BL6、BN6设置求和公式，使得当B2中的月份是4时，求和累计1-4月，当月份是5时，求和累计1-5月，以此类推。谢谢！

miaojohn45

=SUMIF(OFFSET(C4,,,,MONTH(B2)*5),BK4,OFFSET(C6,,,,MONTH(B2)*5))
其余公式同理：预算，同期，公式中BK4换成BL4,BN4即可。
前提是日期得写正确：

hugeinfo

=SUMPRODUCT((--SUBSTITUTE(LOOKUP(COLUMN($C$3:$AA$3),COLUMN($C$3:$AA$3)/($C$3:$AA$3<>""),$C$3:$AA$3),"月","")<=MONTH(--SUBSTITUTE($B$2,"年","")))*($C$4:$AA$4=BK$4)*$C6:$AA6)

swl4561255

=SUMIF($C4:$AA4,BK4,$C6:$AA6)
=SUMIF($C4:$AA4,BL4,$C6:$AA6)
=BK6/BL6
=SUMIF($C4:$AA4,BN4,$C6:$AA6)
=(BK6-BN6)/BN6

fsw0812

=SUMPRODUCT(IFERROR(IF($C$4:$AA$4=BK4,$C$6:$AA$6),0))

hxj119

=SUM(OFFSET($B6,,ROW(INDIRECT("$1:$"&MONTH($B$2)))*5-5+COLUMN(A1)))

sm629

你的office可以试试新函数，另，为何B2单元格不用标准日期格式？还要用text函数提取月份
BK6:=LET(a,TEXTBEFORE(TEXTAFTER($B$2,"/"),"/"),SUM(TAKE(WRAPROWS($C6:$AA6,5),a,1)))
BL6:=LET(a,TEXTBEFORE(TEXTAFTER($B$2,"/"),"/"),SUM(DROP(TAKE(WRAPROWS($C6:$AA6,5),a,2),,1)))
BN6:=LET(a,TEXTBEFORE(TEXTAFTER($B$2,"/"),"/"),SUM(DROP(TAKE(WRAPROWS($C6:$AA6,5),a,4),,3)))

fsw0812

=SUM(IF(OFFSET($B$3,1,1,,MONTH($B$2)*5)=BN$4,OFFSET($B$3,3,1,,MONTH($B$2)*5)))

areshsia

搞定了谢谢各位大哥