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

	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:
https://www.chicagocomputerclasses.com/excel-classes/