Search code examples
excelexcel-formulamaxcriteriamin

Multiple criteria find min and max date values

I browsed for hours so far and couldn't find a solution for this problem. Would appreciate help here. I am newbie.

I am trying to find max and min dates using excel 2016 without microsoft 365 subscription so I don't have MINIFS function I would like use any functions which are available in excel 2016.

Build Country type year Date
A NA Build 2020 7/8/2020
B NA Build 2020 3/23/2020
A NA Build 2021 3/23/2020
B NA Build 2021 11/16/2020
A NA Build 2020 8/28/2020
B EU Build 2020 1/13/2020
A EU Build 2020 11/20/2020
B EU New 2020 7/17/2020
A NA Build 2020 3/28/2020
A NA Build 2020
A EU New 2020 3/16/2020
B NA New 2021 11/4/2020
A NA New 2021 5/4/2020
B NA New 2021 9/28/2020


Max Date Min Date
A NA Build 2020 8/28/2020 3/28/2020

For min Date I would like to ignore blanks.

enter image description here

Solution

  • Option without array entered formulas.

    For Max:

    =AGGREGATE(14,6,(A19=$A$2:$A$15)*(B19=$B$2:$B$15)*(C19=$C$2:$C$15)*(D19=$D$2:$D$15)*E2:E15,1)

    For Min:

    =AGGREGATE(15,6,(1/(--(ISNUMBER($E$2:$E$15))*(A19=$A$2:$A$15)*(B19=$B$2:$B$15)*(C19=$C$2:$C$15)*(D19=$D$2:$D$15)))*$E$2:$E$15,1)

    enter image description here