Results 1 to 15 of 16
Thread: Help with counting dates formula

20141211, 22:42 #1
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 489
 Thanks
 78
 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

20141211, 23:24 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,490
 Thanks
 377
 Thanked 1,473 Times in 1,340 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
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20141211, 23:52 #3
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 489
 Thanks
 78
 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

20141212, 06:02 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 Posts
Try this:
=SUMPRODUCT(($J$8:$J$1108="Closed")*($H$8:$H$1108< =$F$8:$F$1108))Regards,
Rory
Microsoft MVP  Excel

20141212, 06:05 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,689
 Thanks
 122
 Thanked 668 Times in 609 Posts
Being that the posted file is .xls, are you running Excel 2003? I might be wrong but Countifs does not work in 2003.

20141212, 07:39 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 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

20141212, 09:08 #7
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,490
 Thanks
 377
 Thanked 1,473 Times in 1,340 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

20141213, 03:38 #8
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 489
 Thanks
 78
 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

20141213, 07:22 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,953
 Thanks
 140
 Thanked 519 Times in 495 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

20141214, 18:58 #10
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 489
 Thanks
 78
 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

20141214, 23:23 #11
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,689
 Thanks
 122
 Thanked 668 Times in 609 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

20141215, 00:38 #12
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 489
 Thanks
 78
 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

20141215, 04:05 #13
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 Posts
Regards,
Rory
Microsoft MVP  Excel

20141216, 01:59 #14
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 489
 Thanks
 78
 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

20141216, 07:12 #15
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 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
Regards,
Rory
Microsoft MVP  Excel