EXCEL 101: MAXIMUM/ MINIMUM IF WITH MULTIPLE CRITERIA

MAX IF and MIN IF Formula

As we know, SUMPRODUCT(array1, [array2], [array3], ...) uses an array but in Max and Min Function we can't use it since there is no array command directly on Excel 2013 and later.

If we need to find the maximum or minimum value in a range of cells based on specific criteria. We need to create an array formula combining the ‘MAX’ (or ‘MIN’) function with an ‘IF‘ function. By entering our array formula we need to change how do we input formulas from simply "ENTER" to "CTRL + SHIFT + ENTER".

The Formula

{=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values)))}

Finding the Minimum Cost of an Apple" which is "New".

{=MIN(IF(B6:B10=G6,IF(C6:C10=H6,D6:D10)))}

Finding the Maximum Cost of an Apple" which is "New". 

{=MAX(IF(B6:B10=G11,IF(C6:$C$10=H11,D6:D10)))} 

Formula Concept

For finding the Minimum Cost of the Fruit "Apple", the formula works like this.

{True, True, True, True, True}

For finding the Minimum Cost of the Fruit "Apple" with Category "New":

{True, False, True, False, False}

As the cost will returned for each item in this range only if the value two arrays are True:

{Php. 23.00, False, Php. 45.00, False, False}

The Min Function returned the lowest price on our range.


Note: You shouldn't do manually the formula by simply entering { }. The formula will not work.