This post describes a basic user defined function that searches multiple ranges and adds corresponding values.

 

 

 

 

 

 

 

 

 

 

You can also use multiple search values.

VBA code:

Function SumifAMS(lookup_value As Range, ParamArray cellranges() As Variant)
Dim i As Integer, rng1 As Variant, temp As Single, a As Boolean
Dim rng2 As Variant, value As Variant, j As Single
If (UBound(cellranges) + 1) Mod 2 <> 0 Then
  MsgBox "The number of range arguments must be even. 2, 4 , 8 ... and so on"
  Exit Function
End If
For i = LBound(cellranges) To UBound(cellranges) Step 2
    If cellranges(i).Rows.Count <> cellranges(i + 1).Rows.Count Then
        MsgBox "The number of rows in range arguments don´t match."
    End If
    If cellranges(i).Columns.Count <> 1 Then
        MsgBox "Range arguments can only have size one column each."
        Exit Function
    End If
    rng1 = cellranges(i).value
    rng2 = cellranges(i + 1).value
    For j = LBound(rng1) To UBound(rng1)
        For Each value In lookup_value
            If UCase(rng1(j, 1)) = UCase(value) Then a = True
        Next value
        If a = True Then temp = temp + rng2(j, 1)
        a = False
    Next j
Next i
SumifAMS = temp
End Function

 

Where to copy vba code?

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste vba code.
  4. Exit visual basic editor

How to use custom function

 

 

 

 

Download excel file

Sumif across multiple sheets.xls
Excel 97-2003 *.xls