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 + _
            WorksheetFunction.SumIf(wkb.Sheets(Arg).Range(CritRng.Address), _
            Crit, wkb.Sheets(Arg).Range(SumRng.Address))
    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.
  5. Return to Microsoft Excel (Alt+Q).
  6. Save the workbook.

How to Use the Custom Function

Sheet1 Sheet2 Sheet3 Summary
Sum Based on a Single Criteria across Multiple Sheets with a Custom Function - Sheet1 Sum Based on a Single Criteria across Multiple Sheets with a Custom Function - Sheet2 Sum Based on a Single Criteria across Multiple Sheets with a Custom Function - Sheet3 Sum Based on a Single Criteria across Multiple Sheets with a Custom Function - Summary Sheet

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.

Sample Workbook:  Download

Home | Excel | VBA | Blog | About | Contact