Excel VBA - Formula Counting Unique Value error

Question

I am trying to calculate the count of Unique values based on a condition.

For example,

For a value in column B, I am trying to count the Unique values in Column C through VBA.

I know how to do it using Excel formula -

 =SUMPRODUCT((B2:B12<>"")*(A2:A12=32)/COUNTIF(B2:B12,B2:B12))

that value for 32 is dynamic - Programmatically I am calling them inside my vba code as Name

This is my code :

Application.WorksheetFunction.SumProduct((rng <> "") * (rng2 = Name) / CountIfs(rng, rng))

This is the sample data with the requirement

Alternatively, I Concatenated both the columns for keeping it simple and hoping to identify the Unique values which starts with name* method.

I don't know where I am going wrong. Kindly share your thoughts.

When you call a worksheet function from VBA all arguments must be passed comma separated as a ParamArray. What you are trying to do, of course, is passing nested worksheet functions, CountIfs within Sumproduct. I suspect it can actually be done, but it is so complicated that I never found interest in learning how to do it. It would be preferable over comparable VBA functions only when the database is quite large. — Variatus
– Variatus, Commented Apr 10, 2017 at 3:07
@Mat'sMug Yes but I am still struck in this - How do you identify the count of Unique values in a column which starts with say - John ? — Student of the Digital World
– Student of the Digital World, Commented Apr 10, 2017 at 3:29
That's a different problem altogether - one that your question does not describe. Please edit your question so that it can be answered appropriately - see minimal reproducible example. — Mathieu Guindon
– Mathieu Guindon, Commented Apr 10, 2017 at 3:32
Yes @Mat'sMug I edited my question now Sir as an alternative method. I believe that will reduce the complexity of the work but still I am struck with a formula for that. Please help me if you can. — Student of the Digital World
– Student of the Digital World, Commented Apr 10, 2017 at 3:35

Subodh Tiwari sktneer · Accepted Answer · 2017-04-10 13:37:14Z

1

You may try something like this...

Function GetUniqueCount(Rng1 As Range, Lookup As String) As Long
Dim x, dict
Dim i As Long, cnt As Long
Set dict = CreateObject("Scripting.Dictionary")
x = Rng1.Value
For i = 1 To UBound(x, 1)
    If x(i, 1) = Lookup Then
        dict.Item(x(i, 1) & x(i, 2)) = ""
    End If
Next i
GetUniqueCount = dict.Count
End Function

Then you can use it like below...

=GetUniqueCount($A$2:$B$10,C2)

Where A2:B10 is the data range and C2 is the name criteria.

edited Apr 10, 2017 at 13:37

answered Apr 10, 2017 at 13:15

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Subodh Tiwari sktneer Over a year ago

Added another screenshot. It returned the correct output.

Student of the Digital World Over a year ago

It worked Sir. My mistake. Sorry, I had a typo in the data.

Subodh Tiwari sktneer Over a year ago

No problem. :) Glad it worked for you. Please take a moment to accept the solution to mark your question as Solved.

Absinthe · Accepted Answer · 2017-04-10 13:04:40Z

0

I'd put the values into an array, create a temporary 2nd array and only add values to this array if they are not already present, and then replace the original array. Then it's just a simple matter to sum the unique values:

Sub Unique

dim arr(10) as variant, x as variant
dim arr2() as variant

for x = 1 to 10 ' or whatever
   arr(x) = cells(x, 1) ' or whatever
next x

arr2 = UniqueValuesArray(arr)

' now write some code to count the unique values, you get the idea

End Sub

Function UniqueValuesArray(arr As Variant) As Variant()

Dim currentRow, arrpos As Long
Dim uniqueArray() As Variant
Dim x As Long

arrpos = 0
ReDim uniqueArray(arrpos)

For x = 0 To UBound(arr)
    If UBound(Filter(uniqueArray, arr(x))) = -1 Then
        ReDim Preserve uniqueArray(arrpos)
        uniqueArray(arrpos) = arr(x)
        arrpos = arrpos + 1
    End If
Next x

UniqueValuesArray = uniqueArray

End Function

edited Apr 10, 2017 at 13:04

answered Apr 10, 2017 at 12:49

Absinthe

3,4218 gold badges37 silver badges75 bronze badges

1 Comment

Student of the Digital World Over a year ago

It throws an error saying. Cannot assign to an array ! I am trying to debug this.

Collectives™ on Stack Overflow

Excel VBA - Formula Counting Unique Value error

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related