Hi Dave, thanks for your prompt reply, I have modified the code to the
following and get the 'type mismatch' error.

Dim Res1 As Integer
Res1 =
Worksheets("sheet1").Evaluate("=sumproduct((--(am1:am30000=date(2009,08,01)),--(am1:am30000<date(2008,9,1)),BN1:BN30000)))")
MsgBox Res1


note: I had tried just specifing the worksheet with my old sumproduct code
but still got the object required error.



"Dave Peterson" wrote:

Ps. I should have been more clear on this portion:

(am1:am30000=""=08/01/2009"")

You're mixing the =sumif() style of comparing strings with a plain old
comparison (using =).

Dave Peterson wrote:

I would guess that you want to check a specific worksheet for those ranges:

worksheets("sheetnamehere").evaluate("sumproduct(. ..

ps.

It looks like you're checking for a date in a range. I think that using =date()
is less ambiguous:

...(--(am1:am30000=date(2009,08,01)),--(am1:am30000<date(2008,9,1)),

or even
...(--(text(am1:am30000,""yyyymm"")=""200808""), ...

(watch the double quotes. I didn't test the syntax.)

Lman wrote:

i am getting the above error when trying to run the following code

Dim Res1 As Integer
Res1 =
Worksheet.Evaluate("=sumproduct(--(am1:am30000=""=08/01/2009""),--(am1:am30000=""<09/01/2009""),(BN1:BN3000))")
MsgBox Res1

I am using excel 2007...

--

Dave Peterson

--

Dave Peterson