# Thread: Help with counting dates formula

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

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!

HTH

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

Regards

4. Try this:
=SUMPRODUCT((\$J\$8:\$J\$1108="Closed")*(\$H\$8:\$H\$1108< =\$F\$8:\$F\$1108))

5. Being that the posted file is .xls, are you running Excel 2003? I might be wrong but Countifs does not work in 2003.

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

7. Rory,

Thanks! I'd missed that completely.

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

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

zeddy

10. 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 *(.... : ......="??"))

Regards

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

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

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 *(.... : ......="??"))

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.

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

Page 1 of 2 12 Last

#### Posting Permissions

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