xlApp.Evaluate("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef5.Address & _
",3)=""SME""))")

test out the string portion in the immediate window

shtRef = "Sheet1"
set CntRef5 = Range("B9:B50")
? ("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef5.Address & _
",3)=""SME""))")
' which produces the string
=SUMPRODUCT(--(LEFT('[Release Plan
(1,2,3,4).xls]Sheet1'!$B$9:$B$50,3)="SME"))

Now Test it with Evaluate
? Evaluate("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef5.Address & _
",3)=""SME""))")
'Produces
4

Which is correct in my sheet.

--
Regards,
Tom Ogilvy