Geek Answers Handbook

Find a MIN / MAX date in a range if it matches the criteria of other columns

Column A - Date

Column B is the criterion

I want to find the MIN date for each criterion. I tried using Ctrl + Shift + Enter with

 =MIN(MATCH(B2,B:B,0))

but this is not entirely correct, because I need to somehow refer to Column A to get the date. I am sure that this can be done using arrays, so any help would be great.

+3
excel excel-formula
source share
4 answers

Try this (array formula):

 =MIN(IF(B2=B:B,A:A))
+11
source share

An even more compact array formula:

 =MINIF(B2=B:B,A:A)

NOTE 1. Finish using Ctrl+Shift+Enter to enter the formula as an array formula .

NOTE 2: The two-formula method (i.e. using =MIN(IF(B2=B:B,A:A)) ) is more flexible and works in more cases than the one-formula method shown here, but I included it as an answer as a possible option.

+1
source share
 =SMALL(INDEX(($F$2:$F$14=F3)*$D$2:$D$14,),SUM(COUNTA(F:F)-COUNTIF(F:F,F3)))

If your criteria are repeated and you want to find the minimum date for this, you can use it without Shift + Ctrl + Enter.

  • date - column D
  • criterion - column F
+1
source share

= SMALL (INDEX (($ F $ 2: $ F $ 14 = F3) * $ D $ 2: $ D $ 14,), AMOUNT (ACCOUNTS (F :) -COUNTIF (F :, F3)))

This worked for me when I used one criteria, but I need it to look at two criteria, so I combined the two identifiers instead of using a text field, and when I did this, it returned #Num! mistake. I formatted the cell accordingly. Can someone help me add the 2nd criterion to the above formula or explain why my formula doesn’t like the above formula?

0
source share

More articles:


All Articles