Add Only Distinct Positive Values with VBA

In case you are wondering how you can add only positive & unique values then this article is for you.

In this article, you will learn how to add only distinct positive values in excel using VBA code.

Question): I want a macro to sum all of the distinct positive numbers in a specified range which includes negative & positive numbers.

Following is the snapshot of the data we have:

We need to follow the below steps:

• Click on Developer tab
• From Code group select Visual Basic

Enter the following code in the standard module:-

Function AddUnique(ByRef inputrange As Range, _

Optional IgnoreText As Boolean = True, _

Optional IgnoreError As Boolean = True, _

Optional IgnoreNegativenumbers As Boolean = True)

Dim distinctnumbers As Double

Dim cell As Range

Dim dict As Object

Set dict = CreateObject("Scripting.dictionary")

distinctnumbers = 0

    For Each cell In inputrange.Resize(inputrange.Rows.Count, 1)

        cval = cell.Value

        If IgnoreText Then

           If Not (VBA.IsNumeric(cval)) Then cval = 0

        Else

           AddUnique = CVErr(0)

           Exit Function

        End If

        If IgnoreError Then

           If IsError(cval) Then cval = 0

        Else

           AddUnique = CVErr(1)

           Exit Function

        End If

        If IgnoreNegativenumbers Then

           If cval < 0 Then cval = 0

        Else

           AddUnique = CVErr(2)

           Exit Function

        End If

        If Not dict.Exists(cval) Then

           dict.Add cval, cval

           distinctnumbers = distinctnumbers + cval

        End If

    Next

AddUnique = distinctnumbers

End Function

• In cell C2 the formula would be
• =AddUnique(A2:A6)

• To check whether code ignores repeated value or not; let us change the value in cell A1 to 50 & see the result.

In this way we can add distinct positive values through macro code.

 Download-Sample File-xlsm