Results 1 to 6 of 6

Thread: Return date when count = n
Thanks Thanks: 0 Likes Likes: 0

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

    Default Return date when count = n

    I'm trying to create a formula that will return the corresponding date when the sum of cells containing "Q" equals a specified value, i.e. when the count of cells in column B =2, the date 4/22/18 would be given:

    2/17/18 Q
    4/22/18 Q
    7/8/18 --
    10/20/18 Q

    Thanks for the assistance!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Return date when count = n

    Hi & welcome to MrExcel.
    How about

    ABC
    101/09/20195
    202/09/2019q10/09/2019
    303/09/2019q
    404/09/2019q
    505/09/2019
    606/09/2019
    707/09/2019q
    808/09/2019
    909/09/2019
    1010/09/2019q
    1111/09/2019
    1212/09/2019
    1313/09/2019q
    1414/09/2019q
    1515/09/2019q
    1616/09/2019q
    1717/09/2019
    1818/09/2019
    1919/09/2019
    2020/09/2019

    RC



    Worksheet Formulas
    CellFormula
    C2=AGGREGATE(15,6,A1:A20/(B1:B20="Q"),C1)

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,984
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Return date when count = n

    I will take the Fluff example as a reference.
    If you do not have the AGGREGATE function, you can use the following Array formula.


     ABC
    1DateQValues
    201/09/2019 5
    302/09/2019Q10/09/2019
    403/09/2019Q 
    504/09/2019Q 
    605/09/2019  
    706/09/2019  
    807/09/2019Q 
    908/09/2019  
    1009/09/2019  
    1110/09/2019Q 
    1211/09/2019  
    1312/09/2019  
    1413/09/2019Q 
    1514/09/2019Q 
    1615/09/2019Q 
    1716/09/2019Q 
    1817/09/2019  
    1918/09/2019  
    2019/09/2019  
    2120/09/2019  

    CellArray Formula
    C3{=INDEX(A2:A21,SMALL(IF(B2:B21="Q",ROW(B2:B21)),C2)-ROW(B1))}


    Array formulas
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself.
    Regards Dante Amor

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,984
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Return date when count = n

    I will take the Fluff example as a reference.
    If you do not have the AGGREGATE function, you can use the following Array formula.

     ABC
    1DateQValues
    201/09/2019 5
    302/09/2019Q10/09/2019
    403/09/2019Q 
    504/09/2019Q 
    605/09/2019  
    706/09/2019  
    807/09/2019Q 
    908/09/2019  
    1009/09/2019  
    1110/09/2019Q 
    1211/09/2019  
    1312/09/2019  
    1413/09/2019Q 
    1514/09/2019Q 
    1615/09/2019Q 
    1716/09/2019Q 
    1817/09/2019  
    1918/09/2019  
    2019/09/2019  
    2120/09/2019  

    CellArray Formula
    C3{=INDEX(A2:A21,SMALL(IF(B2:B21="Q",ROW(B2:B21)),C2)-ROW(B1))}


    Array formulas
    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 DanteAmor; Oct 1st, 2019 at 11:22 AM.
    Regards Dante Amor

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

    Default Re: Return date when count = n

    That does the trick. Thanks!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Return date when count = n

    Not sure which of us your talking to, but glad we could help & thanks for the feedback.
    Last edited by Fluff; Oct 1st, 2019 at 12:32 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •