. . . it was simply an accurate
observation that the responses did not deal with 5 of the six comparison
operators that COUNTIF is designed to accommodate. . . .
Accurate, perhaps, but nevertheless obtuse with respect to <>. While it's
strictly true that Chip's =SUM(--EXACT(A1:A10,"A")) handled only equality, it's
also true that adapting this for inequality should have been trivially easy for
you: =SUM(1-EXACT(A1:A10,"A")). That you chose to point out what you perceived
as the limitations of the response rather than address those limitations
constructively is characteristic.
Fine & dandy that you've updated your function library, but there's still ample
room for improvement, even in the recently updated ArrayCountIf. Given the array
{"a";"b";"C";"d";"e";"f";"G";"h";"i";"J";"k";"l"} in S1:S12, the formula
=arraycountif(S1:S12,"Z","<",TRUE)
returns 3 (the correct result), but the formula
=arraycountif((S1:S5,S7:S12),"Z","<",TRUE)
returns 1 (which misses "G" and "J", so incorrect). At least the built-in
COUNTIF function chokes on multiple area ranges and returns error values when
fed them as 1st argument. The problem in your function is (underscores for
formatting)
'Ensure that InputArray is an array or multi-cell range
If Not IsArray(InputArray) Then
__Msg = "The first argument to this function must be . . . "
__If CalledFromWorksheet Then ArrayCountIf = Msg Else MsgBox Msg, 16
__Exit Function
Else
__arr = InputArray 'Converts an input range to an array
End If
First the philosopical question: why the heck can't the first argument be a
scalar? COUNTIF(A$2:A2,SomeOtherRange) is a fairly common idiom, when filled
down the 1st arg becomes multiple cell, but it's well-defined when the 1st arg
is single cell. I realize you do this because you iterate through the resulting
array further down in your udf, but it's unnecessary on multiple levels.
Consider.
'Ensure that InputArray is an array or multi-cell range
If Not IsArray(InputArray) Then
__InputArray = Array(InputArray)
End If
Then further down where you iterate through arr, iterate through InputArray.
For Each elem In InputArray
'whatever
Next elem
and use the default Value property of Range objects. Wouldn't this be cleaner?
Wouldn't this be more consistent with COUNTIF?
But I do have to say that your ArrayCountIf is one really ugly monster. It's way
too large and way too complicated. Here's an alternative. Does more than
ArrayCountIf in fewer than 1/3 the number of lines of code. You need to learn
efficient use of subroutines.
Function acountif( _
a As Variant, _
mp As Variant, _
Optional ci As Boolean = True _
) As Variant
'-----------------------------------------------
Dim rv As Variant
Dim i As Long, j As Long, k As Long, m As Long, n As Long
If Not TypeOf a Is Range And Not IsArray(a) Then a = Array(a)
If TypeOf mp Is Range Then mp = mp.Areas(1).Value
If Not IsArray(mp) Then mp = Array(mp)
m = UBound(mp, 1) - LBound(mp, 1)
On Error Resume Next
n = -1
n = UBound(mp, 2) - LBound(mp, 2)
Err.Clear
On Error GoTo 0
If n >= 0 Then
ReDim rv(0 To m, 0 To n)
For i = 0 To m
For j = 0 To n
rv(i, j) = acountif_sub(a, mp(i + LBound(mp, 1), j + LBound(mp, 2)), ci)
Next j
Next i
Else
ReDim rv(0 To m)
For i = 0 To m
rv(i) = acountif_sub(a, mp(i + LBound(mp, 1)), ci)
Next i
End If
If n = -1 And m = 0 Then rv = rv(0) 'scalar
acountif = rv
End Function
Private Function acountif_sub( _
a As Variant, _
mp As Variant, _
ci As Boolean _
) As Long
'--------------------------------------------------------------
'There are times when even udfs should make use of subroutines.
'--------------------------------------------------------------
Dim op As String, x As Variant, mps As String, mpe As Variant, cm As Long
If TypeOf a Is Range And (ci Or IsNumeric(mp) Or IsError(mp) Or _
TypeName(mp) = "Boolean") Then
For Each x In a.Areas
acountif_sub = acountif_sub + Application.WorksheetFunction.CountIf(x, mp)
Next x
Else
cm = IIf(ci, vbTextCompare, vbBinaryCompare)
mps = CStr(mp)
If Len(mps) = 0 Then
op = ""
ElseIf Len(mps) = 1 Then
op = "="
If mps = "=" Then mps = ""
ElseIf InStr(1, " <= <> >= ", " " & Mid(mps, 1, 2) & " ") > 0 Then
op = Mid(mps, 1, 2)
mps = Mid(mps, 3)
ElseIf InStr(1, " < = > ", " " & Mid(mps, 1, 1) & " ") > 0 Then
op = Mid(mps, 1, 1)
mps = Mid(mps, 2)
Else
op = "="
End If
If InStr(1, " #REF! #DIV/0! #NULL! #VALUE! #NAME? #NUM! #N/A ", _
" " & mps & " ") > 0 Then mpe = Evaluate(mps)
For Each x In a
If IsError(mpe) Then
If op = "=" Then
acountif_sub = acountif_sub + IIf(mpe = x, 1, 0)
ElseIf op = "<>" Then
acountif_sub = acountif_sub + IIf(mpe <> x, 1, 0)
End If
ElseIf op = "=" And StrComp(CStr(x), mps, cm) = 0 Then
acountif_sub = acountif_sub + 1
ElseIf op = "<>" And StrComp(CStr(x), mps, cm) <> 0 Then
acountif_sub = acountif_sub + 1
ElseIf op = "<" And StrComp(CStr(x), mps, cm) < 0 Then
acountif_sub = acountif_sub + 1
ElseIf op = "<=" And StrComp(CStr(x), mps, cm) <= 0 Then
acountif_sub = acountif_sub + 1
ElseIf op = ">=" And StrComp(CStr(x), mps, cm) >= 0 Then
acountif_sub = acountif_sub + 1
ElseIf op = ">" And StrComp(CStr(x), mps, cm) > 0 Then
acountif_sub = acountif_sub + 1
End If
Next x
End If
End Function
This should reproduce the same results as COUNTIF if fed the same arguments. If
so, acountif is a proper generalization of COUNTIF. Anything less is kinda
pointless. One known limitation: for array 1st arguments and range 1st arguments
for case-sensitive matching, wildcards aren't supported. If you want to add
wildcard support for case-insensitive matching when the 1st arg is an array, go
for it.
