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:

image 1


We need to follow the below steps:


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


image 2


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


           AddUnique = CVErr(0)

           Exit Function

        End If


        If IgnoreError Then

           If IsError(cval) Then cval = 0


           AddUnique = CVErr(1)

           Exit Function

        End If

        If IgnoreNegativenumbers Then

           If cval < 0 Then cval = 0


           AddUnique = CVErr(2)

           Exit Function

        End If

        If Not dict.Exists(cval) Then

           dict.Add cval, cval

           distinctnumbers = distinctnumbers + cval

        End If


AddUnique = distinctnumbers

End Function


image 4



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


image 5


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


image 6


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


Excel Download-Sample File-xlsm


Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>