有个关于数据汇总的小问题

xsox

还请大师们指点指点，主要是想把名称中的列按条件大于0或者小于0分开汇总。见附件

part98

=SUMIFS(B2:B15,A2:A15,F2,B2:B15,">0")

无关岁月

1. =SUMIFS($B:$B,$A:$A,$F2,$B:$B,IF(COLUMN(A1)=1,">0","<0"))

复制代码

xsox

part98 发表于 2024-8-6 17:31
=SUMIFS(B2:B15,A2:A15,F2,B2:B15,">0")

谢谢老师解答

小凡、、、

------------

飞天篮球猪

飞天篮球猪

博爱风雨同舟

=SUMIFS(B2:B15,A2:A15,F2:F3,B2:B15,{">0","<0"})
一步到位

zhangcheng6688

学习一下字典，打扰了
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   If arr(i, 2) > 0 Then
      d(arr(i, 1)) = d(arr(i, 1)) + arr(i, 2)
   Else
      d1(arr(i, 1)) = d1(arr(i, 1)) + arr(i, 2)
   End If
Next
brr = [f1].CurrentRegion
For i = 2 To UBound(brr)
   brr(i, 2) = d(brr(i, 1))
   brr(i, 3) = d1(brr(i, 1))
Next
[f1].CurrentRegion = brr
End Sub

沈默00

=SUMIFS($B:$B,$A:$A,$F2,$B:$B,{">0","<0","<>0"})  下拉