Article updated on April 19, 2018

The MAXIFS function allows you to calculate the highest value based on a condition or criteria.

Formula in cell G4:

=MAXIFS(D3:D9, B3:B9, G2,C3:C9, G3)

The formula in cell G4 in picture above extracts the amounts that correspond to region Asia and category B. Only three values match: 100, 50 and 65.

The largest of the three values is 100.

Excel Function Syntax

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments

max_range Required. A cell reference to numbers which the highest value will be calculated.
criteria_range1 Required. The cell range you want to apply a specific condition to.
criteria1 Required. The condition you want to use.
[criteria_range2] Optional. Additional cell ranges.
[criteria2] Optional. Additional criteria.

Comments

You can enter up to 126 range/criteria pairs.

The MAXIFS function was introduced in Excel 2016. Use the MAX function and  logical expressions if you have an earlier Excel version.