Author: Oscar Cronquist Article last updated on January 20, 2018

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