VBA UDF SUMIF with Array Parameters I was trying to give SUMIFS3D User-Defined-Function a nice review and ended up getting pretty side-tracked trying to figure out how to pass some parameters by array and whether or not I could send a 3D-range through the function (seems not).

It's a good one for review, I'm sure there are improvements - I wrote it in about 3 hours.

Function

SUMIF3D(ByVal sum_range As Range, ByVal list_Sheets As String, ByVal criteriaRange As Range, ByVal criteria As Variant, Optional ByVal isNumeric As Boolean = False)


Returns: Long

Input

Something like

=SUMIF3D(D1:D5,"sheet1,sheet2",H1:H5,I1)


To set it up just open a workbook and input:

Sheet1!D1:D5 = 1-5
Sheet2!D1:D5 = 10-50
Sheet1!H1:H5 = 1,5,10,15,20


And whatever arguments you want in Sheet1!I1=I5 e.g.


1   2   5   15  15
&gt;0   &gt;10 &gt;=10    &lt;10 20
&gt;10


Or try your luck with matching strings.

It works on everything I've tried. It's a bad mama-jama

Option Explicit

Public Function SUMIF3D(ByVal sum_range As Range, ByVal list_Sheets As String, ByVal criteriaRange As Range, ByVal criteria As Variant, Optional ByVal isNumeric As Boolean = False) As Long
    Const OPERATORS As String = "&gt;,&lt;,&lt;&gt;,="
    Dim isPossible As Boolean
    Dim toSum() As Boolean
    Dim i As Long
    Dim j As Long

    Dim sumRangeCells() As Long
    ReDim sumRangeCells(1 To sum_range.Count, 1 To 2)
    Dim cell As Range
    i = 1
    For Each cell In sum_range
        sumRangeCells(i, 1) = cell.Row
        sumRangeCells(i, 2) = cell.Column
        i = i + 1
    Next

    Dim numberOfCells As Long
    Dim sheetsArray As Variant
    sheetsArray = Split(list_Sheets, ",")
    Dim sumRangeArray As Variant
    numberOfCells = (UBound(sheetsArray) + 1) * sum_range.Count
    ReDim sumRangeArray(1 To numberOfCells)

    Dim k As Long
    k = 1
    For i = LBound(sheetsArray) To UBound(sheetsArray)
        For j = 1 To sum_range.Count
            sumRangeArray(k) = Sheets(sheetsArray(i)).Cells(sumRangeCells(j, 1), sumRangeCells(j, 2))
            k = k + 1
        Next
    Next

    Dim critRangeArray As Variant
    critRangeArray = criteriaRange.Value2
    Dim criteriaArray As Variant
    criteriaArray = criteria.Value2

    ReDim toSum(1 To UBound(critRangeArray, 1))
    If Not IsArray(criteriaArray) Then
        If IsEmpty(criteriaArray) Then
            isPossible = False
        Else: isPossible = True
        End If
    Else
        If Not UBound(criteriaArray, 1) = UBound(critRangeArray, 1) Then
            isPossible = False
        Else: isPossible = True
        End If
    End If

    If Not isPossible Then Exit Function

    Select Case isNumeric
        Case 1
            If IsArray(criteriaArray) Then
                For i = 1 To UBound(critRangeArray)
                    If InStr(1, OPERATORS, Left$(criteriaArray(i, 1), 1)) &gt; 0 Then
                        toSum(i) = Application.Evaluate(critRangeArray(i, 1) &amp; criteriaArray(i, 1))
                    Else: toSum(i) = critRangeArray(i, 1) = criteriaArray(i, 1)
                    End If
                Next
            Else
                For i = 1 To UBound(critRangeArray)
                        If InStr(1, OPERATORS, Left$(criteriaArray, 1)) &gt; 0 Then
                            toSum(i) = Application.Evaluate(critRangeArray &amp; criteriaArray)
                        Else: toSum(i) = critRangeArray(i, 1) = criteriaArray
                        End If
                Next
            End If
        Case 0
            If IsArray(criteriaArray) Then
                For i = 1 To UBound(critRangeArray)
                    toSum(i) = critRangeArray(i, 1) = criteriaArray(i, 1)
                Next
            Else
                For i = 1 To UBound(critRangeArray)
                    toSum(i) = critRangeArray(i, 1) = criteriaArray
                Next
            End If
    End Select

    For j = LBound(sheetsArray) To UBound(sheetsArray)
        For i = 1 To UBound(toSum)
            If toSum(i) Then SUMIF3D = SUMIF3D + sumRangeArray(i + j * UBound(toSum))
        Next
    Next
End Function
A couple of things I have noticed (and I haven't tested the code):


If you make the return value a Variant, instead of Long, you can also pass Excel Errors (e.g. #VALUE!) as a result from the UDF. This would be particular useful if IfPossible is False.
You set OPERATORS up as a constant so you can check the validity of an input. But you don't use the string position at all. So, instead of Const OPERATORS As String = "&gt;,&lt;,&lt;&gt;,=", you can simply have Const OPERATORS As String = "&lt;&gt;=" which covers &lt;, &gt;, &lt;&gt; and =.
Having set and used OPERATORS, you only check the 1st character, so &lt;&gt; will never be matched.
You don't want to check for &lt;= and `>=' as well?


Codewise:

    If Not UBound(criteriaArray, 1) = UBound(critRangeArray, 1) Then
        isPossible = False
    Else: isPossible = True
    End If


Can be simplified to

isPossible = (UBound(criteriaArray, 1) = UBound(critRangeArray, 1))


Similarily:

If IsEmpty(criteriaArray) Then
    isPossible = False
Else: isPossible = True
End If


can be simplified to:

isPossible = Not IsEmpty(criteriaArray)


That entire If-Then-Else block can become:

If Not IsArray(criteriaArray) Then
    isPossible = Not IsEmpty(criteriaArray)
Else
    isPossible = (UBound(criteriaArray, 1) = UBound(critRangeArray, 1))
End If


Which, of course, you can turn around to get rid of the Not

If IsArray(criteriaArray) Then
    isPossible = (UBound(criteriaArray, 1) = UBound(critRangeArray, 1))
Else
    isPossible = Not IsEmpty(criteriaArray)
End If


Missing something here: Select Case isNumeric. That is not how the IsNumeric(val as variant) function is used. And given this is a Boolean result, a simple if-then statement will work. I am surprised this runs without throwing an error (I am taking your word for it that it works).

I am not going to go through it here (this answer is now long enough), but that case statement needs some serious rework. You have a lot of repetition and could probably get rid of at least two levels of nesting.