Back to my original post,

Well, there was a typo in that a paren was left off the end (to close the
evaluate function). However, after I fixed that it worked fine.

Sub abc()
For i = 7 To 10
cnt = Evaluate("SUMPRODUCT((Detail!$D$7:$D$500" & _
"=""Offender Missed Call(STaR)"")*(Detail!$B$7:$B$500=Summary!$A$" & _
i & "))")
Worksheets("Summary").Cells(i, "I").Value = cnt

Next i

End Sub



Tested successfully.
--
Regards,
Tom Ogilvy


wrote in message
ups.com...
hello, i though my problem was solved by it is not
i am not able to make my function to work, it is govong me #value! for
a result

here is my sub
-------------------------------------------------------
Sub test()

Dim numberOfViolations As Variant 'if i will declare this as long
getting type mismatch

For i = 7 To 500

If Sheets("Summary").Cells(i, "A").Value = "" Then
Exit Sub
End If
numberOfViolations =
[SUMproduct((Detail!$B$7:$B$500=$A$"&i&")*(Detail!$ D$7:$D$500="Offender
Missed Call (STaR)"))]
Sheets("Summary").Cells(i, "I").Value = numberOfViolations
Next i
End Sub
----------------------------------------------

when compiling it is not giving me any errors, its just simply doesnt
work
gives me #value! as i said before.

PS! what i am trying to do is to count how many entries in detail meet
that criteria from formula, first critera will be alway different and
it will be taken from A7, A8, A9, thats why i dont have a specific name
there and i used $A$"&i&" as one guy suggested from my previous post,
please really need help, thanks