按指定条件统计数据

zhiwen20190717 · 发表于 2024-7-8 14:33

如图中所示描述，谢谢了。

袁勇 · 发表于 2024-7-8 14:41

=SUMPRODUCT(ISNUMBER(FIND($E5,$A$4:$A$12))*(TEXT($B$4:$B$12,"m月")=F$4))

复制代码

袁勇 · 发表于 2024-7-8 14:42

=SUMPRODUCT(ISNUMBER(FIND($E5,$A$4:$A$12))*(MONTH($B$4:$B$12)>=1)*(MONTH($B$4:$B$12)<=3))

复制代码

=SUMPRODUCT(ISNUMBER(FIND($E5,$A$4:$A$12))*(MONTH($B$4:$B$12)>=1)*(MONTH($B$4:$B$12)<=3)*$C$4:$C$12)

复制代码

绝对替补 · 发表于 2024-7-8 14:45

SUMIFS(c:C,A:A,"*美国*",D:d,1)

加一个辅助列, =TEXT(B2,"m")

方法比较笨

hxj119 · 发表于 2024-7-8 14:45

=COUNTIFS($A$4:$A$12,"*"&$E5&"*",$B$4:$B$12,">="&DATE("2024",LEFTB(F$4,2)/1,1),$B$4:$B$12,"<="&EOMONTH(DATE("2024",LEFTB(F$4,2)/1,1),0))

part98 · 发表于 2024-7-8 14:47

=SUM(ISNUMBER(FIND($E5,$A$4:$A$12))*(TEXT($B$4:$B$12,"m月")=F$4))

复制代码

=SUM(ISNUMBER(FIND($E5,$A$4:$A$12))*(MONTH(B4:B12)<=3))

复制代码

=SUM(ISNUMBER(FIND($E5,$A$4:$A$12))*(MONTH(B4:B12)<=3)*C4:C12)

复制代码

hxj119 · 发表于 2024-7-8 14:49

本帖最后由 hxj119 于 2024-7-8 14:50 编辑

包含美国1-3月总次数：
=COUNTIFS($A$4:$A$12,"*"&$E12&"*",$B$4:$B$12,">="&DATE("2024",LEFTB("1月",2)/1,1),$B$4:$B$12,"<="&EOMONTH(DATE("2024",LEFTB("3月",2)/1,1),0))

总数量：
=SUMIFS($C$4:$C$12,$A$4:$A$12,"*"&$E12&"*",$B$4:$B$12,">="&DATE("2024",LEFTB("1月",2)/1,1),$B$4:$B$12,"<="&EOMONTH(DATE("2024",LEFTB("3月",2)/1,1),0))

windshc · 发表于 2024-7-8 15:24

=IFERROR(SUM(FILTER($C$4:$C$12,ISNUMBER(FIND($E5,$A$4:$A$12))*MONTH($B$4:$B$12)=COLUMN(A1))),"")

=COUNTA(FILTER(A4:A12,ISNUMBER(FIND($E5,$A$4:$A$12))))

=IFERROR(SUM(FILTER($C$4:$C$12,ISNUMBER(FIND($E5,$A$4:$A$12)))),"")

逐渐陌生 · 发表于 2024-7-8 17:06

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

[已解决] 按指定条件统计数据

评分