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

• Mathieu Guindon: Please take the time to present your code. Describe the inputs, how it works, how it's used, why it's needed, etc.
• PG_Develop: Thanks, Mat's. Added description. Please, tell me, if I need to put some additional data
• Máté Juhász: I don't see why it would run slowly. Did you tried debugging to see where it lags? How slow exactly it is? How many times do you run it? on how many sheets? How big the ranges are?
• PG_Develop: Hi, @Máté. I debugged this code, and there is no lagging.. Now it sums full columns ranges from two spreadsheets. This function is used in 360 cells. If I do two sumifs on those spreadsheats manually and then sum their results (for all 360 cells), model works fast. But if I put this macro, which combines those manual sumifs, it starts to work much slower (1-2 sec instead of 0,5 sec)
• Máté Juhász: I suppose you've a lot of formulas in your workbook, disabling calculation before running the macro might help. (see some tips here: stackoverflow.com/documentation/excel-vba/1107/…)

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.