I would cheat:

With Range("D10")
    .Formula = "=SUMPRODUCT((A2:A10000<>"""")/COUNTIF(A2:A10000,A2:A10000&""""))"
    .Value = .Value
End With

... but at least you know exactly what you are getting and why.

If the formula works, you'll get the value you expect.

Regards, TMS