Data between dates

hiteshkataria · #1 (**permalink**) 02-12-10, 09:23

Hi,

In an excel sheet I have data as shown below:

Code:

Code No.	BillDate	Cost
A00001		14/05/05	435.00
A00002		14/05/05	40.00
A00003		16/05/05	20.00
A00004		14/05/05	435.00
A00005		12/05/05	945.00
A00006		12/05/05	789.00

There are several number of records.

There is an userform, on which user enters two dates & on click of a button, it should display the Sum of cost between these 2 dates:

Suppose user enters 13/05/05 & 14/05/05, it should display 910.00

Please help me to get this using ODBC or something similar.

Thanks.

Colin Legg · #2 (**permalink**) 02-12-10, 10:39

Hi,

In XL2003 or earlier, you can call the Application.WorksheetFunction.SumIf() method (twice) to calculate the relevant sum.
In XL2007 or later, you can call Application.WorksheetFunction.SumIfs() .

If an example would help let me know which version of XL you are using.

hiteshkataria · #3 (**permalink**) 02-12-10, 10:46

I use XL 2003, appreciate if you could provide an example.

Colin Legg · #4 (**permalink**) 02-12-10, 13:07

Okay, let's suppose the data in post#1 is in range A1:C7.

To get the cost on 13/05/05 and 14/05/05, if we were to use a worksheet formula , it would be something like this:
=SUMIF(B2:B7,">=38485",C2:C7)-SUMIF(B2:B7,">38486",C2:C7)

To emulate this in VBA we can use something like the following (of course there are many different ways you can skin a cat). You just have to be a bit careful with UK/American date formats (use VBA.DateValue to get the dates from the textboxes which hold string values) and convert them into doubles (VBA.CDbl) for the SUMIF() calls. This is just a basic template - no error handling or defensive coding included.

Code:

Private Sub CommandButton1_Click()
    Dim StartDate As Date, EndDate As Date
    Dim rngDates As Range, rngCosts As Range
    Dim dblSum As Double
    
    StartDate = DateValue(TextBox1.Text)
    EndDate = DateValue(TextBox2.Text)
    
    
    Set rngDates = Sheet1.Range("B2:B7")
    Set rngCosts = Sheet1.Range("C2:C7")
    
    With Application.WorksheetFunction
        dblSum = .SumIf(rngDates, ">=" & CDbl(StartDate), rngCosts) - .SumIf(rngDates, ">" & CDbl(EndDate), rngCosts)
    End With
    
    MsgBox CStr(dblSum)
    
End Sub

hiteshkataria · #5 (**permalink**) 02-15-10, 08:05

Thanks!

That helped me!

hiteshkataria · #6 (**permalink**) 02-15-10, 08:54

There is another requirement:

For the below data,
a) I need to have the COUNT as well as SUM of Cost, whereever the SoldDate is empty
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'.

Code:

Code No.	BillDate	Cost	SoldDate	SoldBill
A00001		14/05/05	435.00	15/05/05	1234
A00002		14/05/05	40.00
A00003		16/05/05	20.00	16/05/05	R123
A00004		14/05/05	435.00
A00005		12/05/05	945.00	14/05/05	D123
A00006		12/05/05	789.00	14/05/05	234

For this case, Ouput would be:
a) 2 and 475.00
b) For dates 15/05/05 and 16/05/05, 435.00
For dates 14/05/05 and 16/05/05, 1224.00

Colin Legg · #7 (**permalink**) 02-25-10, 17:42

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...

Internet Services

Web Development

Digital Marketing

Consumer Tech