If you put the formula in an empty cell in sheet1, does it work?

If yes, then try that formula in code.

If you can't get the code to work right, share the formula you got working in
the worksheet cell.

You may want to try the other suggestion, too.

Lman wrote:

I can get it to work using the following code

Dim Res1 As Long
Dim Res2 As Long
Res1 =
Worksheets("sheet1").Evaluate("=sumproduct(--(am1:am30000=date(2009,08,01)),BN1:BN30000)")
Res2 =
Worksheets("sheet1").Evaluate("=sumproduct(--(am1:am30000=date(2009,09,01)),BN1:BN30000)")
MsgBox Res1 - Res2

however i can't seem to get it to combine in one statement.

--

Dave Peterson