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

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

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)

insum = 0

For Each n In arr

insum = insum + n

Next n

SUMIFUNIQUENUMS = insum

End Function

Then use it in your worksheet like this

	A	B	C	D	E
1	CA	11		CA	=SUMIFUNIQUENUMS($B$1:$B$7,$A$1:$A$7,D1)
2	NY	14		NY	29
3	NY	15		IN	0
4	CA	11
5	CA	11
6	CA	14
7	CA	11

Sheet1

Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/

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

Locations: