# SUMIFS3D User-Defined-Function

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

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 + _
Next cell
Case 2
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
Next cell
Case 3
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
Next cell
Case 4
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
Next cell
Case 5
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
Next cell
Case 6
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
Next cell
Case 7
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
Next cell

End Select

End Function

• Please take the time to present your code. Describe the inputs, how it works, how it's used, why it's needed, etc. May 11, 2017 at 16:44
• Thanks, Mat's. Added description. Please, tell me, if I need to put some additional data May 11, 2017 at 17:35
• 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? May 12, 2017 at 9:38
• 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) May 12, 2017 at 9:49
• 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/…) May 12, 2017 at 11:26

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

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 + _

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