Like COUNTIF and SUMIF, it is helpful to have a conditional MAX or MIN. Excel doesn’t have it for now (yeah, “for now”, ‘coz MS has steadily been adding such useful functions with every new release).
Well, the good news is that there is a work-around.
MAXIF
The approach is to have an array formula on the IF column returning a bool, and multiplying it with the column from which MAX is to be found.
Say, we need to find the MAX in column F based on a condition on Column A and K5 having the compare value. We can do this as below.
=MAX((A2:A30=K5)*(F2:F30))
Since it is an array formula, do a Ctrl+Shift+Enter and it’ll become {=MAX((A2:A30=K5)*(F2:F30))}. Do NOT manually add the braces.
MINIF
This is very similar to what we did in the above activity. Just replace MAX with MIN.
=MIN((A2:A30=K5)*(F2:F30))
Again, this is an array formula and need to do Ctrl+Shift+Enter.
Note that empty cells in column F might break this formula.