SUMIFS3D User-Defined-Function

Question

I wrote user-defined function in Excel, but it seems to slow down work significantly. Function performs sumifs for several worksheets in workbook.

Could you advise me, please, on potential optimization of it?

Main idea of the function - to create sumifs, which would sum data on the same criterias from different worksheets.

Arguments of function:

wblist - Range with names of worksheets, where sumifs should be performed

SumRng - Range of cells to be summed on each worksheet

Optional CritRangeN - Range of cells to be assessed with criteria (CritN)

How it works 1) Understands, how many criterias were passed to the function 2) Sums sumifs (with already defined number of arguments) for worksheets, which were mentioned in the wblist Range

Code is presented below

Public Function SUMIFS3D(wblist As Range, SumRng As Range, _
                            Optional CritRange1 As Variant, Optional Crit1 As Variant, _
                            Optional CritRange2 As Variant, Optional Crit2 As Variant, _
                            Optional CritRange3 As Variant, Optional Crit3 As Variant, _
                            Optional CritRange4 As Variant, Optional Crit4 As Variant, _
                            Optional CritRange5 As Variant, Optional Crit5 As Variant, _
                            Optional CritRange6 As Variant, Optional Crit6 As Variant, _
                            Optional CritRange7 As Variant, Optional Crit7 As Variant)

    Dim cell As Range
    Dim wkb As Workbook
    Set wkb = Application.Caller.Parent.Parent
    Dim paramN As Integer

    'Application.Volatile
    paramN = 0

    If Not IsMissing(CritRange1) Then
        paramN = paramN + 1
    End If
    If Not IsMissing(CritRange2) Then
        paramN = paramN + 1
    End If
    If Not IsMissing(CritRange3) Then
        paramN = paramN + 1
    End If
    If Not IsMissing(CritRange4) Then
        paramN = paramN + 1
    End If
    If Not IsMissing(CritRange5) Then
        paramN = paramN + 1
    End If
    If Not IsMissing(CritRange6) Then
        paramN = paramN + 1
    End If
    If Not IsMissing(CritRange7) Then
        paramN = paramN + 1
    End If


    Select Case paramN
        Case 1
                For Each cell In wblist
                 SUMIFS3D = SUMIFS3D + _
                 WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
                 wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1)
                Next cell
        Case 2
                For Each cell In wblist
                 SUMIFS3D = SUMIFS3D + _
                 WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
                 wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
                 wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2)
                Next cell
        Case 3
                For Each cell In wblist
                 SUMIFS3D = SUMIFS3D + _
                 WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
                 wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
                 wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
                 wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3)
                Next cell
        Case 4
                For Each cell In wblist
                 SUMIFS3D = SUMIFS3D + _
                 WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
                 wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
                 wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
                 wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
                 wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4)
                Next cell
        Case 5
                For Each cell In wblist
                 SUMIFS3D = SUMIFS3D + _
                 WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
                 wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
                 wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
                 wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
                 wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
                 wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5)
                Next cell
        Case 6
                For Each cell In wblist
                 SUMIFS3D = SUMIFS3D + _
                 WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
                 wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
                 wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
                 wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
                 wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
                 wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5, _
                 wkb.Sheets(cell.Value).Range(CritRange6.Address), Crit6)
                Next cell
        Case 7
                For Each cell In wblist
                 SUMIFS3D = SUMIFS3D + _
                 WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
                 wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
                 wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
                 wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
                 wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
                 wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5, _
                 wkb.Sheets(cell.Value).Range(CritRange6.Address), Crit6, _
                 wkb.Sheets(cell.Value).Range(CritRange7.Address), Crit7)
                Next cell

    End Select

End Function

Answer 1

The only other standard formula that could do this is =SUMPRODUCT(SUMIF(INDIRECT, and I applaud you for going with a UDF over that monstrosity.

Given the purpose of your function and its similarity to two other functions

SUMIF(range, criteria, [sum_range])
SUMIFS(sum_range, criteria_range1, criteria1, ...)

Also note that SUM can go across sheets-

=SUM(Sheet1:Sheet2!D1:D5)

I think it would make sense to follow the (standard) naming conventions prevailing in this function family.

SUMIF3D(sheet_names, sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2])

I might rename sum_range to sum_range3D if you like. The standard way you'd do your argument testing is by passing the array of arguments to a test function.

What you might not be aware of is that MS has some specs on UDFs:

Arguments in a function      255
Length of formula contents   8,192 characters
Cross-worksheet dependency   64,000 worksheets that can refer to other sheets

So, you're in the clear no problem, but it's something to consider if you take an array of arguments.

---

But getting back to using that first standard function I mentioned - you're essentially requiring a list of sheets that you can use indirectly, aren't you?

WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address)

Looks very similar to SUMIFS(INDIRECT( in a loop, eh?

Let's see if we can't get your 3D function to take a 3D Range

NOPE _{Sorry, I tried, I'll probably post a question. I did}

All right, that's too bad.

We'll continue with your variable names - make them a little more concise so it's obvious what they do. Characters are free. Standard VBA naming conventions have camelCase for local variables and PascalCase for other variables and names.

I'd use parameterNumber instead of paramN and I wouldn't use cell but that's up to you. I'd also go with targetWorkbook or maybe ActiveWorkbook or ThisWorkbook depending on your setup

Otherwise, your function is mostly taking arguments to execute the built-in function, which is a difficult task.

Your IsMissing setup is kind of weird. Would CritRange5 be available if CritRange4 is missing? It could be simplified like this

Dim rangeNumber As Long
For rangeNumber = 1 To 7
    If Not IsMissing("CritRange" & i) Then
        parameternumber = parameternumber + 1
    Else: Exit For
Next

And then with your Case selection, that could be simplified as well

For rangeNumber = 1 To parameternumber
        For Each cell In wblist
            SUMIFS3D = SUMIFS3D + _
                Application.WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
                wkb.Sheets(cell.Value).Range("CritRange" & parameternumber.Address), "Crit" & parameternumber)
        Next
    Next

You've eliminated 6/7 of the IF and 6/7 of the SELECT CASE code.