Count Unique Values with CountIF works in Excel but gives type mismatch error in VBA

In VBA the worksheet functions do not behave exactly in the same way. You can use Evaluate, writing the formulas as they are and using range addresses. But a better way would be using a Scripting.Dictionary. Please, try the next code, which will return the unique values number, but also which are these unique values and how many per each (in columns "O:P"):

Sub countUnique()
  Dim sh As Worksheet, lastR As Long, arr, i As Long, dict As Object
  
  Set sh = ActiveSheet
  lastR = sh.Range("N" & sh.rows.count).End(xlUp).row
  arr = sh.Range("N2:N" & lastR).value 'place the range in an array for  faster processing
  Set dict = CreateObject("Scripting.Dictionary")
  dict.CompareMode = TextCompare

  For i = 1 To UBound(arr)
    dict(arr(i, 1)) = dict(arr(i, 1)) + 1
  Next i
  
  Debug.Print dict.count & " unique values"
  sh.Range("O2").Resize(dict.count, 1).value = Application.Transpose(dict.Keys)
  sh.Range("P2").Resize(dict.count, 1).value = Application.Transpose(dict.items)
End Sub

To Evaluate the worksheet function, please try the next piece of code:

Sub CountUniqueEvaluate()
  Dim sh As Worksheet, lastR As Long, rng As Range
  
  Set sh = ActiveSheet
  lastR = sh.Range("N" & sh.rows.count).End(xlUp).row
  Set rng = sh.Range("N2:N" & lastR)
  
   Debug.Print Evaluate("=SUMPRODUCT(1/COUNTIF(" & rng.Address & ", " & rng.Address & "))")
End Sub