1. ## Need formula help

Greetings everyone,
I want to do unique counts of col "a" for each month (a20:a31) using date1 and date2 columns and based on b14:b18 criteria. For example as depicted in the attachment for the month of March year 16 it supposed to output 4.

2. dubdub,

You can use this UDF to count your matches.

In cell B20, enter this formula then copy down and to the right:

=DTcriteria(\$A20)

In a standard module, paste the following code:
Code:
```Public Function DTcriteria(rng As Range) As Integer
'-------------------------------
'DECLARE AND SET VARIABLES
Dim cell As Range, count As Integer
Dim dt1 As Date, dt2 As Date
count = 0
col = Application.Caller.Column
'-------------------------------
'CYCLE THROUGH EACH CELL IN NAMED RANGE
For Each cell In Range("Ref")
Row = cell.Row
'-------------------------------
'GET LAST AN FIRST DATE OF SELECTED MONTH
dt2 = DateValue("1/" & rng & "/" & Cells(18, col))
dt1 = DateSerial(Cells(18, col), Month(dt2) + 1, 0)
'-------------------------------
'TEST IF ALL CRITERIA ARE TRUE
If Cells(Row, "B") = Cells(16, col) And _
Cells(Row, "C") = Cells(14, col) And _
Cells(Row, "D") = Cells(15, col) And _
Cells(Row, "E") = Cells(17, col) And _
Cells(Row, "F") <= dt1 And _
Cells(Row, "G") >= dt2 Then
count = count + 1
End If
Next cell
'-------------------------------
'RETURN COUNT
DTcriteria = count
End Function```
dubdub1.png

Note: Don't forget the "\$" in =DTcriteria(\$A20)

HTH,
Maud

3. Great thanks Maud for your help, I think, my first post does not detail the uniqueness part enough, which intended not to consider the count of any repetitive name in col A for the same month more than once.

For example if the name “RBP-50” is shown twice at two different dates in March, then it should be considered and counted as one in March.

Is there a chance for a formula solution, or a generic UDF to ease the implementation process on any data size.

4. DubDub,

Using Maud's fine code you can make it more generic by using NamedRanges for the search criteria and splitting your data onto a separate sheet and using a Dynamic Range Name for it then everything will auto adjust as data is added. A quick search of this forum will turn up many examples. HTH

5. RG,

Forgot to mention that in my code, there is a named range called "Ref" that everything references.

Dubdub,

I agree with RG that if you moved your criteria/month table to another work sheet, or at least o different columns instead of stacked, it would be more manageable from a code standpoint.

Maud

#### Posting Permissions

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