Search code examples
excelvbaunique

CountIf and SumProduct in Excel VBA

I am trying to count the number of unique values in a table column. I have set the range as the relevant column in the table. In defining the number of unique values to count I am getting the following error:

Required_Rows = WorksheetFunction.SumProduct(1 / WorksheetFunction.CountIf(Range(Rng), Range(Rng)))

Solution

  • You won't be able to use Sumproduct and SumIf in that manner. Try using the Evaluate method instead...

    Dim Required_Rows As Variant 'declared as Variant in case Evaluate returns error

Required_Rows = Evaluate("SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))")

If Not IsError(Required_Rows) Then
    'do something
Else
    'do something else
End If

    Note that Evaluate has a 255 character limit.

    EDIT

    With the Range object you've defined (ie. Rng), try...

    Required_Rows = Evaluate("SUMPRODUCT(1/COUNTIF(" & Rng.Address(external:=True) & "," & Rng.Address(external:=True) & "))")

    Hope this helps!