Blog

Excel VBA Function to SUM Unique Numbers Only – Unique SUMIF Function

November 23, 2016/in Excel, Reference, Tutorial/by admin

First add this code to your VBA Function

Function SUMIFUNIQUENUMS(rng2 As Range, rng1 As Range, rng3 As Range)

    num = rng1.Count - 1
    Dim arr1() As Variant
    ReDim arr1(num)
    i = 0
    For Each r In rng1
    
        If r.Value = rng3.Value Then
            arr1(i) = 1 * rng2(i + 1)
        Else
            arr1(i) = 0
        End If
        i = i + 1
        
    Next r
    
    Dim arr As New Collection, a
    
    On Error Resume Next
    
      For Each a In arr1
         arr.Add a, Str(a)
      Next
    
    
      insum = 0
      
        For Each n In arr
            insum = insum + n
        Next n
        
        SUMIFUNIQUENUMS = insum

End Function

Then use it in your worksheet like this