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.