UDF Required

[Asheesh]

Hi Guys,

I have the formula solution but was trying to create a UDF for the same..Only UDF solution
Note: We just need to add only the distinct positive values from the range and ignore the text or error values if any

 

[NARAYANK991]

Hi Asheesh ,

Try the following :

Public Function AddUniques(ByRef inputrange As Range, Optional IgnoreText As Boolean = True, Optional IgnoreError As Boolean = True, Optional IgnoreNegativenumbers As Boolean = True)
                Dim sumuniques As Double
                Dim cell As Range
                Dim dict As Object
                Set dict = CreateObject("Scripting.Dictionary")
              
                sumuniques = 0
                For Each cell In inputrange.Resize(inputrange.Rows.Count, 1)
                    currval = cell.Value
                    If IgnoreText Then
                       If Not (VBA.IsNumeric(currval)) Then currval = 0
                    Else
                       AddUniques = CVErr(0)
                       Exit Function
                    End If
                  
                    If IgnoreError Then
                       If IsError(currval) Then currval = 0
                    Else
                       AddUniques = CVErr(1)
                       Exit Function
                    End If
                  
                    If IgnoreNegativenumbers Then
                       If currval < 0 Then currval = 0
                    Else
                       AddUniques = CVErr(2)
                       Exit Function
                    End If
                  
                    If Not dict.Exists(currval) Then
                       dict.Add currval, currval
                       sumuniques = sumuniques + currval
                    End If
                Next
                AddUniques = sumuniques
End Function

Narayan

 

[Asheesh]

Thank you for the help..