Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    22

    Unanswered: Data between dates

    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.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.
    Last edited by Colin Legg; 02-12-10 at 10:43.

  3. #3
    Join Date
    Feb 2010
    Posts
    22
    I use XL 2003, appreciate if you could provide an example.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2010
    Posts
    22
    Thanks!

    That helped me!

  6. #6
    Join Date
    Feb 2010
    Posts
    22
    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

  7. #7
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    There are many ways of doing these.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •