I have a whole bunch of data on a whole bunch of sheets and for each of them, I want to find the count of the unique values in a given column.

When I use the following command in a sheeet, it works perfectly

```
=SUM(IF(FREQUENCY(MATCH(REST!D2:D2225,REST!D2:D2225,0),MATCH(REST!D2:D2225,REST!D2:D2225,0))>0,1))
```

But when I use an equivalent command inside VBA, I am getting errors

```
range1 = Cell(2,j).Address & ":" & Cells(k,j).Address
= Application.WorksheetFunction.SUM( Application.WorksheetFunction.IF( Application.WorksheetFunction.FREQUENCY( Application.WorksheetFunction.MATCH(Range(range1),Range(range1),0), Application.WorksheetFunction.MATCH(Range(range1),Range(range1),0))>0,1))
```

I have tried other combinations, like using

```
Application.Match
```

and

```
Application.Frequency
```

I’ve also got “type mismatch errors.

On the other hand, the following function works perfectly

```
Application.Worksheetfunction.Sum(Range(range2))
```

The big difference between range1 and range2 is that range2 data is strictly numeric while range1 data is both numeric and string.

**EDIT: TO implement BX201’s solution**

```
range_TradeID_total_FL = .Cells(2, TradeId_column).Address & ":" & Cells(Finalrow, TradeId_column).Address
doubleQ = Chr(34) & Chr(34)
fStr = "=SUMPRODUCT((range_TradeID_total_FL & " <> " & " & doubleQ & ")/COUNTIF(range_TradeID_total_FL , range_TradeID_total_FL & " & " & " & doubleQ & "))"
var_TOTAL_TradeId_count_FL = Evaluate(fStr)
MsgBox var_TOTAL_TradeId_count_FL
```

I don’t want to store the values in a cell but store it in a variable. But when I do that, the MsgBox gives me the value “True” instead of a number.

@SiddharthRout I am not aware of evaluate. Can you please tell me how it would work in my context, especially when I use variables for range.

Try this (UNTESTED)

This is an example where row is a variable.

```
Sub Sample()
Dim r1 As Long, r2 As Long
Dim formulaString As String
r1 = 2
r2 = 2225
'=SUM(IF(FREQUENCY(MATCH(REST!D2:D2225,REST!D2:D2225,0),MATCH(REST!D2:D2225,REST!D2:D2225,0))>0,1))
formulaString = "=SUM(IF(FREQUENCY(MATCH(REST!D" & r1 & _
":D" & r2 & _
",REST!D" & r1 & _
":D" & r2 & _
",0),MATCH(REST!D" & r1 & _
":D" & r2 & _
",REST!D" & r1 & _
":D" & r2 & _
",0))>0,1))"
Debug.Print Application.Evaluate(formulaString)
End Sub
```

### Answer：

A much simpler formula for getting count of unique values in a column is:

`=SUMPRODUCT((REST!$D$2:$D$2225<>"")/COUNTIF(REST!$D$2:$D$2225,REST!$D$2:$D$2225&""))`

Normal formula so no need to CSE. This works best if there are no conditions attached to your values, like maybe a simple list of names or values.

I think evaluating this should give you the result you want. You can also assign it as a formula to a cell. Either of the following two approaches works, in that regard.

**Insert formula into cell**

```
Sub UniqueCount1()
doubleQ = Chr(34) & Chr(34)
fStr = "=SUMPRODUCT((REST!D2:D2225<>" & doubleQ & ")/COUNTIF(REST!D2:D2225,REST!D2:D2225&" & doubleQ & "))"
Range("C1").Formula = fStr
End Sub
```

**Evaluate the formula and insert result into cell**

```
Sub UniqueCount2()
doubleQ = Chr(34) & Chr(34)
fStr = "=SUMPRODUCT((REST!D2:D2225<>" & doubleQ & ")/COUNTIF(REST!D2:D2225,REST!D2:D2225&" & doubleQ & "))"
Range("C1").Value = Evaluate(fStr)
End Sub
```

A third one approach is to use a scripting dictionary. This is a bit more complicated, but it’s pretty fast and can be used in a myriad of ways.

```
Sub UniqueDict()
Dim oDict As Object
Dim sElem As Variant, sList As Variant
sList = ThisWorkbook.Sheets("REST").Range("D2:D2225").Value
Set oDict = CreateObject("Scripting.Dictionary")
With oDict
For Each sElem in sList
If Not .Exists(sElem) And Not IsEmpty(sElem) Then
.Add sElem, Empty
End If
Next sElem
End With
MsgBox oDict.Count
End Sub
```

Hope this helps.

EDIT:

Here’s an approach using your variables.

```
startRow = 2 'Or whatever it is based on your other code.
finalRow = 2225 'Or whatever it is based on your other code.
rngStr = "REST!$D$" & Startrow & ":$D$" & Finalrow '$D$2:$D$2225
dQ = Chr(34) & Chr(34) 'Double quote string.
fStr1 = "=SUMPRODUCT((" & rngStr & "<>" & dQ & ")" '=SUMPRODUCT((REST!$D$2:$D$2225<>"")
fStr2 = "/COUNTIF(" & rngStr & "," & rngStr & "&" & dQ & "))" '/COUNTIF($D$2:$D$2225,$D$2:$D$2225&""))
fStr = fStr1 & fStr2 '=SUMPRODUCT((REST!$D$2:$D$2225<>"")/COUNTIF($D$2:$D$2225,$D$2:$D$2225&""))
var_Total = Application.Evaluate(fStr)
MsgBox var_Total
```

Hope this helps.

Tags: class, excel-vbaexcel, function, vba