Excel MAX IF formula to find largest value with conditions

This article explores various methods to determine the maximum value in Excel based on specified conditions. Previous tutorials covered the basic MAX function; this article delves into more advanced scenarios using different formulas.

Methods covered include:

• MAX IF with single and multiple conditions
• MAX IF without array formulas
• MAX IF with OR logic
• The MAXIFS function (Excel 2019 and later)

Excel's MAX IF Formula

Prior to Excel 2019, achieving conditional maximums required array formulas combining MAX and IF. The general structure is:

{=MAX(IF(criteria_range=criteria, max_range))}

This formula needs to be entered using Ctrl Shift Enter to function correctly. The curly brackets {} indicate an array formula. For example, to find Jacob's highest long jump score (names in A2:A10, distances in C2:C10):

=MAX(IF(A2:A10="Jacob", C2:C10))

The formula creates an array of TRUE/FALSE values based on the condition, then returns the maximum value from the max_range where the condition is TRUE.

MAX IF with Multiple Criteria

For multiple conditions, nested IF statements or the multiplication operator can be used within the array formula:

Nested IF: {=MAX(IF(criteria_range1=criteria1, IF(criteria_range2=criteria2, max_range)))}

Multiplication (AND logic): {=MAX(IF((criteria_range1=criteria1)*(criteria_range2=criteria2), max_range))}

MAX IF without Arrays

To avoid array formulas, the SUMPRODUCT function can be used:

=SUMPRODUCT(MAX((criteria_range1=criteria1)*(criteria_range2=criteria2)*max_range))

This formula avoids array entry but requires numeric data in the max_range.

MAX IF with OR Logic

To find the maximum value when any of several conditions is true, use the addition operator ( ) within the array formula:

{=MAX(IF((criteria_range1=criteria1) (criteria_range2=criteria2), max_range))}

A non-array equivalent using SUMPRODUCT is also possible, but requires handling non-numeric values appropriately.

MAXIFS Function (Excel 2019 and later)

Excel 2019 and later versions offer the MAXIFS function, simplifying conditional maximum calculations:

=MAXIFS(max_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

This function directly handles multiple criteria and doesn't require array formulas.

This article provides a comprehensive guide to finding maximum values under various conditions in Excel, catering to different Excel versions and user preferences. A practice workbook is available for download.

The above is the detailed content of Excel MAX IF formula to find largest value with conditions. For more information, please follow other related articles on the PHP Chinese website!