您可以使用vba并创建UDF:
Public Function countArr(rng As Range, crit1 As String, crit2 As String) As Integer
Application.Volatile
Dim rngArr() As Variant
Dim i As Long, j As Long, h1 As Boolean, h2 As Boolean
rngArr = rng
For i = LBound(rngArr, 1) To UBound(rngArr, 1)
h1 = False
h2 = False
For j = LBound(rngArr, 2) To UBound(rngArr, 2)
If rngArr(i, j) = crit1 And crit1 = crit2 Then
If Not h1 Then
h1 = True
Else
h2 = True
End If
ElseIf rngArr(i, j) = crit1 Then
h1 = True
ElseIf rngArr(i, j) = crit2 Then
h2 = True
End If
Next j
If h1 And h2 Then countArr = countArr + 1
Next i
End Function
将其放在工作簿附带的文件中,而不是工作表代码或此工作簿代码中:
然后,您可以像其他任何公式一样称呼它:
=countArr($B$2:$F$6,B$9,$A10)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句