Get Min if Criteria is met - For Query

  • Hello everyone,



    I make a query base on multiple tables. One of my Table has a multiple entry with the same ID, example:
    [TABLE="class: grid, width: 300, align: left"]

    [tr]


    [TD="align: center"]ID[/TD]
    [TD="align: center"]Date (mm/dd/yy)[/TD]
    [TD="align: center"]Amount[/TD]

    [/tr]


    [tr]


    [td]

    17-001

    [/td]


    [td]

    02/21/17

    [/td]


    [TD="align: right"]0.00[/TD]

    [/tr]


    [tr]


    [td]

    17-001

    [/td]


    [td]

    04/20/17

    [/td]


    [TD="align: right"]1,000.00[/TD]

    [/tr]


    [tr]


    [td]

    17-001

    [/td]


    [td]

    05/19/14

    [/td]


    [TD="align: right"]500.00[/TD]

    [/tr]


    [/TABLE]








    I want to get the date 04/20/17 in one of my Field in Query since it has >0 of Amount and it is the next date after 02/21/17.


    I used Min in Total of my Query but the result is 02/21/17.


    Is there any way to come up with the result i wanted for? What is the Expression builder if in case its possible...

  • Re: Get Min if Criteria is met - For Query


    Hello,


    Should your data be located in range A1:C4 ... and your Id in cell E1 ...


    you could use the following Array Formula : (Use simultaneously the 3 keys Ctrl+Shift+Enter ... instead of the Enter key)


    Code
    1. =MIN(IF($A$2:$A$4=E1,IF($C$2:$C$4>0,$B$2:$B$4)))


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)