MAXIFS, MINIFS Functions

The MAXIFS and MINIFS functions are an extension of the MAX and MIN functions to include a conditional component in their functionality. MAXIFS returns the maximum value of all cells that meet the specified criteria. MINIFS returns the minimum value of all cells that meet the specified criteria. You can specify more than one set of criteria to determine which data is selected to be part of the evaluation.

Syntax

MAXIFS

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

MINIFS

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments – similar for both functions

Argument	Description
max_range (MAX function) min_range (MIN function)	Required. The actual range of cells for which we want the maximum or minimum value determined.
criteria_range1	Required. The range that is evaluated using criteria1 . This is part of the first range/criteria pair.
criteria1	Required. This is the criteria used to determine which cells in criteria_range1  will be used to filter the data. This can be a number, expression, or text. For example, criteria can be entered as 40, ">40", C6, "bolts", or "125".
criteria_range2, criteria2, ...	Optional. You can have additional range/criteria pairs, up to a maximum of 127 total pairs.

The max_range (or min_range) and criteria_range arguments must have the same number of rows and columns, otherwise, the function will return the #VALUE! error.

Example

In this example, we want to produce reports that show the minimum and maximums sales per state. However, we only want to evaluate entries with 10 or more orders (>=10). So, we have two criteria that we want to use to determine the data to be evaluated.

Formulas

We use the following formulas to return the desired results.

Maximum:

=MAXIFS($D$2:$D$12,$B$2:$B$12,F3,$C$2:$C$12,G3)

Minimum:

=MINIFS($D$2:$D$12,$B$2:$B$12,F10,$C$2:$C$12,G10)

Formula explanation

We have used identical cell references and criteria arguments for both functions, so they can be described together.

MAXIFS($D$2:$D$12,$B$2:$B$12,F3,$C$2:$C$12,G3)

• The first argument for both functions is a reference to the Sales column, D2:D12. This is the actual range we want to evaluate for the minimum and maximum values. An absolute reference has been used - $D$2:$D$12 . 
• The Criteria_range1 argument is the State column B2:B12. This is part of the first range/criteria pair we’ll use to establish our first condition. An absolute cell reference has been used - $B$2:$B$12 . 
• The Criteria1 argument is F2 . This is a cell reference to our first criteria, the name of the state which is “New York” in the case of cell F2. A cell reference has been used to hold the value to make it easier to change in future if we so desire. This has been left as a relative reference because we want the references to change relative to cells the formula is copied to. 
• The Criteria_range2 argument is the # of Orders column, C2:C12. This is part of the second range/criteria pair. An absolute reference has been used here - $C$2:$C$12 . 
• The Criteria2 argument is cell G2 which represents the criteria “>=10”. This is part of the second range/criteria pair used to filter the data to be evaluated. A cell reference has been used to make it easier to update with different criteria values.

To display the results, we enter the MAXIFS formula in cell H2 and use the Fill Handle of the cell to copy the formula down to H5 . This calculates the maximum sales for the other states.

For the minimum values, we enter the MINIFS formula in cell H10 and use the Fill Handle to copy the formula down to H13 to calculate the minimum sales for the other states.