排除单据包含F ,求和

jisijie630 · 发表于 2024-11-21 19:27

=SUMIFS(J:J,I:I,B2,H:H,"<>*F")

忙到飞起 · 发表于 2024-11-21 21:35

=SUM(FILTER($J$2:$J$16,ISERR(SEARCH("f",$H$2:$H$16))*($I$2:$I$16=B2)))

写的不好

wpsghost · 发表于 2024-11-22 13:47

=LET(arr,FILTER(I2:J16,ISERR(FIND("F",H2:H16))),brr,TAKE(arr,,1),crr,UNIQUE(brr),HSTACK(crr,BYROW(crr,LAMBDA(x,SUM(FILTER(TAKE(arr,,-1),brr=x))))))

守候_CJ · 发表于 2024-11-22 23:58

=SUMIFS($J$2:$J$16,$I$2:$I$16,B2,$H$2:$H$16,"<>*F*")

waternew · 发表于 2024-11-23 08:33

=SUM((1-ISNUMBER(FIND("F",H2:H16)))*(I2:I16=B2)*(J2:J16))

Excel地板砖 · 发表于 2024-11-23 09:43

=SUM(TEXT(IFERROR(FIND("F",$H$2:$H$16),-1),"!0;1")*($I$2:$I$16=B2)*$J$2:$J$16)

zhangcheng6688 · 发表于 2024-12-26 10:39

学习一下字典
Sub test()
arr = [h1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
If InStr(arr(i, 1), "F") = 0 Then
d(arr(i, 2)) = d(arr(i, 2)) + arr(i, 3)
End If
Next
brr = [b1].CurrentRegion
For i = 2 To UBound(brr)
brr(i, 2) = d(brr(i, 1))
Next
[b1].CurrentRegion = brr
End Sub

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

[求助] 排除单据包含F ,求和

评分