You need to do it like you would on a worksheet, not from within VBA:

cnt = Evaluate("SUMPRODUCT((LEFT(Detail!$D$7:$D$500,10)" & _
"=""Tx Missing"")*(Detail!$B$7:$B$500=Summary!$A$" & _
i & "))")

HTH,
Bernie
MS Excel MVP

wrote in message
oups.com...
hello, i was wondering if it would be possible to use LIKE somehow
within my sumproduct here is example

Sub TxMissing()
Dim nameOfViolation As String

For i = 7 To 500
If Sheets("Summary").Cells(i, "A").Value = "" Then
Exit Sub
End If
cnt = Evaluate("SUMPRODUCT((Detail!$D$7:$D$500" & _
"=""Tx Missing"")*(Detail!$B$7:$B$500=Summary!$A$" & _
'<---------- THIS LINE
i & "))")
Worksheets("Summary").Cells(i, "L").Value = cnt
Next i

End Sub
-----------------------------------------------------------------
where it says Tx Missing it is not always appears as Tx Missing,
sometimes it would have certain numbers after that sentence, and they
would be always random, for example Tx Missing-15678, or Tx
Missing-14786, so how would I specify in my macro that i need it to
look not for exact words, but for a sentece that starts with Tx
Missing.....
i have tryed to use < signs instead of = will work in my case for
this function, but i have similar funtions that i will need to create
and they are not workting properly :( please help me :)