Array formula for unique dates for specific names
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Array formula for unique dates for specific names

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Array formula for unique dates for specific names

    Hello,

    I am trying to count the number of unique dates between 1/1/2018 and 12/31/2019 for specific Well names. For example, Well Name (29-6 64N) has 5 rows of dates in which 4 of those are unique and the other is repeated. Is there a way to count how many dates are unique for each Well Name?

    Well Name is in column B with 1039 rows
    Dates are in column I with 1039 rows

    keep in mind that I have a sheet without repeated Well Names so now im just trying to add how many times the names are repeated by using the dates as a reference

    thank you

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Array formula for unique dates for specific names

    Hi,

    Below should work for you:

    ABCDE
    1WellDate
    229-6 64N7/12/201929-6 64N4
    329-6 64N7/12/2019
    429-6 64N7/12/2019
    529-6 64N7/12/2019
    629-6 64N7/12/2019
    729-6 64N7/12/2019
    829-6 64N7/13/2019
    929-6 64N7/14/2019
    1029-6 64N7/15/2019
    1129-6 32N7/16/2019
    1229-6 32N7/14/2019
    1329-6 32N7/15/2019
    1429-6 32N7/15/2019
    1529-6 32N7/15/2019
    1629-6 32N7/15/2019
    1729-6 32N7/15/2019
    1829-6 32N7/15/2019
    1929-6 32N7/15/2019

    Sheet2



    Array Formulas
    CellFormula
    E2{=SUM(IF(FREQUENCY(IF($A$2:$A$19=D2,B2:B19),B2:B19),1))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Last edited by Aryatect; Jul 12th, 2019 at 02:51 PM.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array formula for unique dates for specific names

    Thank you so much!!!

  4. #4
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Array formula for unique dates for specific names

    Glad could help!
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array formula for unique dates for specific names

    By any chance do you know where to add a criteria where it does the same thing but in a date range??

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array formula for unique dates for specific names

    Quote Originally Posted by Aryatect View Post
    Glad could help!
    I don't know how to tag people so you can see this haha

  7. #7
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Array formula for unique dates for specific names

    Hi, Sorry missed this, will this work?

    ABCDEF
    1WellDate
    229-6 64N7/12/201929-6 64N7/12/20191
    329-6 64N7/12/2019
    429-6 64N7/12/2019
    529-6 64N7/12/2019
    629-6 64N7/12/2019
    729-6 64N7/12/2019
    829-6 64N7/13/2019
    929-6 64N7/14/2019
    1029-6 64N7/15/2019
    1129-6 32N7/16/2019
    1229-6 32N7/14/2019
    1329-6 32N7/15/2019
    1429-6 32N7/15/2019
    1529-6 32N7/15/2019
    1629-6 32N7/12/2019
    1729-6 32N7/20/2019
    1829-6 32N7/15/2019
    1929-6 32N7/15/2019

    Sheet2



    Array Formulas
    CellFormula
    F2{=SUM(IF(FREQUENCY(IF(($A$2:$A$19=D2)*($B$2:$B$19=E2),$B$2:$B$19),$B$2:$B$19),1))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array formula for unique dates for specific names

    How would it be for like "<"&Date(2019,1,1) so basically everything before 2019

  9. #9
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Array formula for unique dates for specific names

    so in place of $B$2:$B$19=E2 use $B$2:$B$19 < Date(2019,1,1)

    Code:
    
    
    Code:
    =SUM(IF(FREQUENCY(IF(($A$2:$A$19=D2)*($B$2:$B$19 < Date(2019,1,1)),$B$2:$B$19),$B$2:$B$19),1))
    


    Enter with Ctrl+Shift+Enter
    Last edited by Aryatect; Jul 19th, 2019 at 11:21 AM. Reason: adding full formula
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array formula for unique dates for specific names

    the issues is that i have dates from 2018 in between those of 2019

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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