There are many ways of doing these.
Quote:
|
a) I need to have the COUNT as well as SUM of Cost, whereever the SoldDate is empty
|
The formula for each would be:
=COUNTIF(D2:D7,"=")
=SUMIF(D2:D7,"=",C2:C7)
The COUNTIF() formula literally counts empty cells, so it assumes that D2:D7 is the exact range. If the requirement is really that you want the sum and count whenever SoldDate is empty AND BillDate is not empty then, in XL 03, you could use SUMPRODUCT instead:
=SUMPRODUCT(--(ISNUMBER(B2:B7)),--(D2:D7=""))
=SUMPRODUCT(--(ISNUMBER(B2:B7)),--(D2:D7=""),C2:C7)
To emulate the COUNTIF() and SUMIF() examples in VBA, you can call the COUNTIF and SUMIF functions via the worksheetfunction object as demonstrated earlier in the thread.
To emulate the more complicated SUMPRODUCT() expressions these in VBA you can use the worksheet object's evaluate method, for example:
Code:
Sub foo()
Debug.Print CStr(Sheet2.Evaluate("SUMPRODUCT(--(ISNUMBER(B2:B7)),--(D2:D7=""""),C2:C7)"))
End Sub
Quote:
|
b) From startDate to endDate (as mentioned in your code) of SoldDate, I need the SUM of Cost, whereever SoldBill doesn't begin with 'R' or 'D'.
|
Here's one formula approach which gives a result of 435:
=SUMPRODUCT(--(LEFT(E2:E7,1)<>"R"),--(LEFT(E2:E7,1)<>"D"),--(D2:D7>=DATE(2005,5,15)),--(D2:D7<=DATE(2005,5,16)),C2:C7)
Again, you can emulate in VBA via the Evaluate method:
Code:
Sub foo()
Debug.Print CStr(Sheet2.Evaluate("SUMPRODUCT(--(LEFT(E2:E7,1)<>""R"")," & _
"--(LEFT(E2:E7,1)<>""D""),--(D2:D7>=DATE(2005,5,15))," & _
"--(D2:D7<=DATE(2005,5,16)),C2:C7)"))
End Sub
Hope that helps...