In this article, we will create a User Defined Function (UDF) or custom function to add unique values in a defined range.
Raw data for this article consists of random numbers in seven different samples.
Add another column to find the sum of values in each row.
“Total Sum” column displays the sum of numbers in each row.
Now, add another column to find the sum of only unique numbers in each row. We have created a User Defined Function “AddDistinctValues” to find the sum of unique numbers. Add formula =AddDistinctValues(A11:G11) in Cell I11 and drag the formula to other rows.
UDF “AddDistinctValues” will give the sum of unique numbers in the defined range. In the image below, one can easily see the difference between the total sum and the sum of unique numbers.
UDF “AddDistinctValues” takes range object as input. It adds up unique numbers in the input range.
To find unique numbers in the input range, we have used collection object. As we know collection cannot store duplicate values, when we insert numbers in the input range in the collection, duplicate numbers won’t be re-inserted in the collection, instead an error will be generated.
For handling the error, we have used “On Error Resume Next” statement. This statement won’t halt the function on encountering the error, instead Loop will move to the next statement.
Once we have the unique numbers in the collection, we can add them in array.
Please follow below for the code
Function AddDistinctValues(InputRange As Range) As Integer 'Declaring variables Dim Rng As Range Dim UniqueValues As New Collection Dim UniqueValue As Variant 'Recalculates the function whenever calculation occur in any other cell Application.Volatile On Error Resume Next 'Adding unique values to collection from input range For Each Rng In InputRange UniqueValues.Add Rng.Value, CStr(Rng.Value) Next On Error GoTo 0 AddDistinctValues = 0 'Adding values in the collection For Each UniqueValue In UniqueValues AddDistinctValues = AddDistinctValues + UniqueValue Next End Function
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org