sumif vba

[av8tordude]

The code that I have doesn't seem to give an accurate result. The result should be $3.20. It suppose to look for dates after 7/1/2012 in column BO and for words "Business" in column BP and add up the numbers it finds. Can some one assist. thank you

Sub mac()
MsgBox Format(WorksheetFunction.SumIf(Range("BO11:BO500"), ">=" & CDate("07/01/" & Year(Date)) + WorksheetFunction.SumIf(Range("BP11:BP500"), "=" & "Business"), Range("BU11:BU500")), "$##,###0.00")
End Sub

[GTO]

I am pretty terrible with worksheet functions, but I don't know if you can put together SUMIF's like that. Does this help?

Sub test()
With Sheet1
MsgBox Format(Evaluate( _
"SumProduct(--(" & .Range("BO11:BO500").Address(0, 0, , -1) & ">=" & CLng(DateSerial(2012, 7, 1)) & "),--(" _
& .Range("BP11:BP500").Address(0, 0, , -1) & "=""Business"")," & .Range("BU11:BU500").Address(0, 0, , -1) & "*1)"), _
"$##,###0.00")
End With
End Sub

[Bob Phillips]

You don't need the *1 Mark!

[GTO]

Hi Bob,

Thank you, I wasn't sure about that part:aw.

Did I catch a missing arg in the two SUMIF's correctly?

[GTO]

ACK! How much do I need to pay for extra edit time? Kidding of course, but this kept at me on the way home. Of course the *1 isn't necessary; it's already an array of values to multiply against (for the corresponding elements of the other resultant 0's or 1's.

Thanks Bob :-)

[Bob Phillips]

Exactly!

[av8tordude]

Thank you both. Very much appreciated!

[av8tordude]

Hi Guys,

Can anyone explain what these numbers (0, 0, , -1) are for. When I implement this code, i get a type mismatch error, but if I change -1 to 0, then I don't get the error.

.Range("BO11:BO500").Address(0, 0, , -1)

[Kenneth Hobs]

Use VBE's F2 to Browse the help for Address or press F1 when your cursor is in or near Address for help.