Find MIN/MAX date in a range if it matches criteri

问题:

Column A is date

Column B is criteria

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

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

but thats not quite right because I need to refer to Column A somehow to get the date. I'm pretty confident this can be done with arrays, so any help would be great.

回答1:

Try this (array formula):

=MIN(IF(B2=B:B,A:A))

回答2:

An even more compact array formula is:

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

NOTE 1: Complete 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 single-formula method shown here but I've included it as an answer as a possible option.

回答3:

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

If your criteria is repeated and want to find the min date for that you can use this without shift+ctrl + enter function.

• date is D column
• criteria is F column