Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Help with counting dates formula

    Hi Loungers,

    I have a spread sheet where column j = Status (Closed, open, draft) Column F = Due Date, Column H= Date Closed all starting at row 8 to row 3000

    I'm trying to calculate the total, where Column j =Closed and where the date in Column H is less than or equal to the date in Column H.

    I hope that makes sense.

    Any thoughts, suggestions would be appreciated.

    Regards

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    This should do the trick:
    =COUNTIFS(J8:J23,"Closed",H8:H23,"<="&F8:F23)
    Of course you'll adjust the 23's to 3000's or better yet create range names and use them!

    verada.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi RetiredGeek

    Not sure if I'm doing something wrong, but not getting results.

    I've attached a sample of the spread sheet with the formula at H2

    Any ideas?

    Thanks for your help

    Regards
    Attached Files Attached Files

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Try this:
    =SUMPRODUCT(($J$8:$J$1108="Closed")*($H$8:$H$1108< =$F$8:$F$1108))
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Being that the posted file is .xls, are you running Excel 2003? I might be wrong but Countifs does not work in 2003.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    RG's formula is only actually comparing the closed dates to the date in F8 (specifically the cell in column F in the same row as the formula is entered). If you alter that F8 cell to a value lower than any closed date, the formula returns 0. Due to the criteria array, it also only works at all if the formula is in rows 8:23, and returns a different value for each row, even when the formula is the same in each. If you enter it in any other row it just returns 0.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Thanks! I'd missed that completely.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks all for you help, this works fine.

    If I need to add another requirement to the formula - say column e ="test" in addition to those below

    =SUMPRODUCT(($J$8:$J$1108="Closed")*($H$8:$H$1108< =$F$8:$F$1108))

    how would I change the formula to account for this extra test?

    Regards

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi verada

    just use..
    =SUMPRODUCT(($J$8:$J$1108="Closed")*($H$8:$H$1108< =$F$8:$F$1108)*($E$8:$E$1108="test"))

    zeddy

  10. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks zeddy - thats what I need.

    I assume that to add more criteria, it's just a matter of adding that to the end with *(.... : ......="??"))

    Thanks for your help

    Regards

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Verada,

    Consider a flexible UDF that will allow you to add an optional condition as you requested in Zeddy's formula. The syntax is:

    =CompDate(CloseDate As Range, DueDateOffset As Long, [Optional Criteria As Variant, Optional CriteriaOffset As Variant])

    Where:
    CloseDate Required. Range of the closing dates- (H8:H1108)
    DuteDateOffset Required. Column offset, in reference to closing dates, for the Due Date (-2)
    Criteria Optional. Criteria to be matched ("test")
    CriteriaOffset Optional. Column offset, in reference to closing dates for the criteria (-3)

    With no optional criteria: =CompDate(H8:H1108,-2)
    This matched Rory's formula:
    =SUMPRODUCT(($J$8:$J$1108="Closed")*($H$8:$H$1108< =$F$8:$F$1108))

    With optional arguments: =CompDate(H8:H1108,-2,"test",-3)
    This matched Zeddy's formula using column E as additional criteria:
    =SUMPRODUCT(($J$8:$J$1108="Closed")*($H$8:$H$1108< =$F$8:$F$1108)*($E$8:$E$1108="test"))

    Place the UDF formula in the cell where you want the result to show. Any changes to the data will update the result. Below is a screen shot of Rory's and Zeddy's formula results against the UDF results.

    Code:
    Public Function CompDate(CloseDate As Range, DueDateOffset As Long, _
            Optional Criteria As Variant, Optional CriteriaOffset As Variant) As Long
        Application.Volatile
    '---------------------------
    'DECLARE AND SET VARIABLES
        Dim cell As Range
    '---------------------------
    'USING OPTIONAL ARGUMENTS
        If IsMissing(CriteriaOffset) = True Then
            For Each cell In CloseDate
                If cell <> "" And cell <= cell.Offset(0, DueDateOffset) Then
                    CompDate = CompDate + 1
                End If
            Next
    '---------------------------
    'NOT USING OPTIONAL ARGUMENTS
        Else
            For Each cell In CloseDate
                If cell <> "" And cell <= cell.Offset(0, DueDateOffset) And _
                               cell.Offset(0, CriteriaOffset) = Criteria Then
                    CompDate = CompDate + 1
                End If
            Next
        End If
    End Function
    UDF with Optional Arguments.png
    Attached Files Attached Files

  12. #12
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thankyou Maudibe - That looks really good.

    I'll plug this into the working spread sheet and go from there.

    Your help is very much appreciated

    Regards

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by verada View Post
    Thanks zeddy - thats what I need.

    I assume that to add more criteria, it's just a matter of adding that to the end with *(.... : ......="??"))

    Thanks for your help

    Regards
    Correct.

    FWIW, a built-in formula should perform better than a UDF but, if you are going to use a UDF, I'd suggest using a ParamArray for the optional arguments so that you can supply as many criteria as you like rather than a limited number.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Rory - I understand some of this, but you have lost me on the ParamArray bit.

    Do you have an example of what this would look like?

    Regards

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Here's a simple example:
    Code:
    Public Function CompDate(CloseDate As Range, DueDateOffset As Long, _
                             ParamArray Criteria() As Variant) As Long
                             
        Dim n                           As Long
        Dim bMatch                      As Boolean
        Dim cell                        As Range
        
        Application.Volatile
    
        For Each cell In CloseDate
            bMatch = False
            If cell <> "" And cell <= cell.Offset(0, DueDateOffset) Then
                bMatch = True
                ' test if optional params were passed
                If UBound(Criteria, 1) > 0 Then
                    ' loop through pairs of params (hence step 2)
                    ' first parameter is the criterion, second is the offset
                    For n = LBound(Criteria, 1) To UBound(Criteria, 1) Step 2
                        If cell.Offset(, Criteria(n + 1)).Value <> Criteria(n) Then
                            bMatch = False
                            Exit For
                        End If
                    Next    'n
                End If
                If bMatch Then CompDate = CompDate + 1
            End If
        Next    ' cell
    End Function
    In reality you'd need checking for proper pairs of parameters and error handling. Also this is case-sensitive as written.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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