# Sum Based on a Single Criteria across Multiple Sheets with a Custom Function

The following custom function returns a sum based on a single criteria across multiple sheets...

Option Explicit

Function SUMIF3D( _
CritRng As Range, _
Crit As Variant, _
SumRng As Range, _
ParamArray ArgList() As Variant)

Dim Arg As Variant
Dim wkb As Workbook

Application.Volatile

If IsMissing(ArgList) Then
SUMIF3D = CVErr(xlErrValue)
Exit Function
End If

Set wkb = Application.Caller.Parent.Parent

For Each Arg In ArgList
SUMIF3D = SUMIF3D + _
Next Arg

End Function

## Where to Put the Code

1. Open the workbook in which to store the code.
2. Open the Visual Basic Editor (Alt+F11).
3. Insert a standard module (Insert > Module).
4. Copy/paste the above code into the module.
6. Save the workbook.

## How to Use the Custom Function

 Sheet1 Sheet2 Sheet3 Summary    The following formulas return the sum of B2:B5 from the specified sheets, where the corresponding value in A2:A5 is equal to the value in A2 of the Summary sheet...

=SUMIF3D(A2:A5,A2,B2:B5,"Sheet1","Sheet2","Sheet3")

or

=SUMIF3D(A:A,A2,B:B,"Sheet1","Sheet2","Sheet3")

Note that each sheet name specified in the formula needs to be enclosed within quotes. Also, the sheet names can be listed in any order.

Based on the sample data, the formulas return 60.