Excel VBA-公式计数唯一值错误 [英] Excel VBA - Formula Counting Unique Value error

问题描述

我正在尝试根据条件计算唯一值的计数.

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

例如

对于B列中的值，我试图通过VBA计算C列中的唯一值.

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

我知道如何使用Excel公式-

I know how to do it using Excel formula -

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

32的值是动态的-我以编程方式在vba代码中将其称为Name

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

这是我的代码:

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

这是具有要求的示例数据

This is the sample data with the requirement

或者，我将这两列连接起来以保持简单，并希望标识以name *方法开头的Unique值.

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.

推荐答案

您可以尝试类似的方法...

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)

其中A2:B10是数据范围，而C2是名称标准.

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

这篇关于Excel VBA-公式计数唯一值错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！