Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files
    TIA
    dubdub

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.
    Last edited by dubdub; 2015-08-30 at 03:01.
    TIA
    dubdub

  4. #4
    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
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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
  •