多条件出现次数统计公式

liuhl511

成123123 发表于 2025-1-14 14:10
=SUM((C3:C301=F3)*(ROUND(D3:D301,0)=G3))
=SUM((ISNUMBER(FIND("FW",C3:C301)))*(ROUND(D3:D301,0)=G6))

太好了，都可以用，非常感谢

Excel_muke

liuhl511 发表于 2025-1-14 14:20
感谢大佬，为啥我把“={COUNT(IF((C:C=F3)*(ROUND(D:D,0)=G3),B:B))}”复制进去单元格提示错误，2个公式 ...

1. =COUNT(IF((C:C=F3)*(ROUND(D:D,0)=G3),B:B))

复制代码

输入这个  左右两边的{}意思是数组函数，需要按三键ctrl+shift+enter才能使用

你是那块小饼干

1. =SUMPRODUCT(($C$3:$C$301=F3)*(ROUND($D$3:$D$301,0)=G3))

复制代码

zhangcheng6688

借楼学习一下字典，打扰了
Sub test()
arr = [b2].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   strkey = arr(i, 2) & Round(arr(i, 3), 0)
   d(strkey) = d(strkey) + 1
Next
lastr = Cells(Rows.Count, "f").End(xlUp).Row
brr = Range("f2:h" & lastr)
For i = 2 To UBound(brr)
   If brr(i, 1) <> "" And brr(i, 2) <> "" Then
      strkey = brr(i, 1) & brr(i, 2)
      brr(i, 3) = d(strkey)
   End If
Next
[f2].Resize(UBound(brr), UBound(brr, 2)) = brr
End Sub