Excel MAX IF formula


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.


{=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.


Excel MAX IF formula 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.