Table of Contents
Summary
The MAX IF formula finds the maximum value from a range when a specific condition is met.
Microsoft Excel does not contain a native “MAXIF” or “MAXIFS” function, but the MAX and IF statements can be used together to solve this problem.
Formula
{=MAX(IF(criteria_range = criteria, average_range))}
Arguments (inputs)
criteria_range = the range of data to test the criteria against
criteria1 = the criteria to test against the range of data
average_array = the range of values from which the max will be selected if the condition(s) are true
Note, when entering this array formula (as indicated by the {} brackets), you must press Ctrl + Shift + Enter.
Return value
The MAX IF formula will return the maximum value from a range when the condition is true. The formula will return a numeric value.
Example
In the example above the formula in cell H6 looks for the maximum price within the range E6:E25, when the category in range C6:C25 is equal to “Furniture” per the reference to cell G6.
=MAX(IF(C6:C25 = G6, E6:E25))
In this example the MAX IF formula returns $261.96 (from row 6), as it is the maximum price of all items of the category furniture.
