用函数实现多条件计数

抹不去の回忆

想要实现的结果，如下图：

hxj119

上传附件！！！

抹不去の回忆

hxj119 发表于 2024-9-6 16:36
上传附件！！！

附件已上传

hxj119

1. =SUM(($A$2:$A$14=F2)*($C$2:$C$14<$D$2:$D$14))

复制代码

moqixinfan

=SUMPRODUCT(($A$2:$A$15=F2)*($D$2:$D$15<$C$2:$C$15))

抹不去の回忆

hxj119 发表于 2024-9-6 16:40

感谢，sum函数还可以这么用，涨知识了

leepoison

=GROUPBY(A2:A12,C2:C12,COUNT,,0,,D2:D12<C2:C12)

沈默00

=SUMPRODUCT(($D$2:$D$12<$C$2:$C$12)*($A$2:$A$12=$J2))

waternew

=SUM(($D$2:$D$12<$C$2:$C$12)*(($A$2:$A$12)=F2))

=SMALL(COUNTIFS($C$2:$C$12,">"&$D$2:$D$12,$A$2:$A$12,F2),1)

zhangcheng6688

学习一下字典，打扰了
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   If Not d.exists(arr(i, 1)) Then
      d(arr(i, 1)) = 0
      If arr(i, 4) < arr(i, 3) Then
         d(arr(i, 1)) = d(arr(i, 1)) + 1
      End If
   Else
       If arr(i, 4) < arr(i, 3) Then
         d(arr(i, 1)) = d(arr(i, 1)) + 1
      End If
   End If
Next
[f1].Resize(, 2) = Array("区域", "小于目标周转天数门店数")
[f2].Resize(d.Count, 2) = Application.WorksheetFunction.Transpose(Array(d.keys, d.items))
End Sub