Like this:

Dim y as Long
y = Evaluate("SUMProduct((A2:A10000<>"""")/COUNTIF(A2:A10000,A2:A10000&""""))")