You need to encase your string variable within quotations and also when using Evaluate you should use .Address

One other point, when using Evaluate widely I'd say it's generally best to qualify the object against which you wish for it to be applied.

COUNTSTRING2 = RNG.Parent.Evaluate("SUMPRODUCT((LEN(" & RNG.Address & ")-LEN(SUBSTITUTE(" & RNG.Address & ",""" & MyStr & """,""""))))/LEN(""" & MyStr & """)")