Results 1 to 9 of 9

Thread: Find Latest value that meets Criteria

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find Latest value that meets Criteria

    I am wondering how can i find the latest information that meets my criteria, because all my table are run form a formula we the "Blank" cells will always read as a')', there fore i am having a Countif, Sumif, Maxif, etc issues, i am probably just typing my formula incorrectly.

    Based on the following table, I need the Red X to equal '5'. Lets say for example i have already collated my data and all I am looking at is Salesmen 'John', lets also preten that John made not sales in Month 3 because he was on vacation, but all i want to know when that last sale was made in this case the '5th' month was his latest sale. as stated all cells will have numbers in them as that infor is fed through other formulas, as you know if a formula returns 'Nil' as a result then a '0' is placed in the sell

    Name John
    Lates sale made 'x' 'th Month
    Name Month Sales Made Sales ($)
    John 1 1 100
    John 2 5 200
    John 3 0 0
    John 4 2 200
    John 5 4 300
    John 6 0 0

  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: Find Latest value that meets Criteria

    Hi, Try Below:

    ABCD
    1NameJohn
    2Lates sale made5'th Month
    3
    4NameMonthSales MadeSales ($)
    5John11100
    6John25200
    7John300
    8John42200
    9John54300
    10John600

    Sheet1



    Worksheet Formulas
    CellFormula
    C2=AGGREGATE(14,6,($A$5:$A$10=$C$1)*($C$5:$C$10>0)*($B$5:$B$10),1)

    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
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Latest value that meets Criteria

    Perfect, i like it.
    Sam_D_Ben

  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: Find Latest value that meets Criteria

    Thanks @Sam_D_Ben
    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
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,197
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find Latest value that meets Criteria

    How about:

    =LOOKUP(2,1/((C5:C10 > 0)*(A5:A10=C1)),(B5:B10))
    Regards Dante Amor

  6. #6
    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: Find Latest value that meets Criteria

    Another option:

    =MAXIFS(B5:B10,A5:A10,C1,C5:C10,">"&0)
    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

  7. #7
    New Member
    Join Date
    Apr 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Latest value that meets Criteria

    Thank you So much! all 3 options work perfectly!,

    #1. =AGGREGATE(14,6,($A$5:$A$10=$C$1)*($C$5:$C$10>0)*($B$5:$B$10),1)
    - By Aryatect

    #2. =LOOKUP(2,1/((C5:C10 > 0)*(A5:A10=C1)),(B5:B10))
    -
    By DanteAmor

    #3. =MAXIFS(B5:B10,A5:A10,C1,C5:C10,">"&0)
    - By Aryatect

    Now depending how complex the overall task is, with over 100 salesmen across 12 different sheets I will try them all and see which works best for larger data bases
    Last edited by HeyItsDizzy; Aug 14th, 2019 at 06:21 PM.

  8. #8
    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: Find Latest value that meets Criteria

    Glad we could help and thanks for the feedback !

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,197
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find Latest value that meets Criteria

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

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
  •