MAXIFS Function. Get the maximum value in a cell that matches multiple criteria.(Microsoft Excel)


Japanese version.

This function prints the largest value in a cell that satisfies multiple criteria; it is an extension of the MAX function.

Unlike the COUNT function and other functions, there is no single criteria function (MINIF).

The MAXIFS function does not exist in versions prior to Excel 2016, so intermediate cells using the IF function must be created or array formula must be used.

How it works

=MAXIFS(Max_range,Criteria1, Criteria_range2, Criteria2 ... Criteria_range127, Criteria127)
Max_rangeRequired argument.Specify the target cell range.
Criteria_range1 - 127Only 1 is required.Specifies a range of cells to be evaluated for the criteria.
Criteria1 - 127Only 1 is required.Specify the criteria to be included in the tally.


For example, it can be used to obtain the maximum value of a specific product category and the maximum value of a product that is eligible for a discount.

It can also be used when a comparison of large and small values is required, such as the maximum value among products with a specific unit price or higher.

An example of obtaining the maximum value among the matches for a specific string.

The following is an example of obtaining the Largest amount of a product that matches the category and is eligible for the discount.

In K4, specify the MAXIFS function as follows


The criteria_range is specified by absolute cell reference, because the reference point does not change even if the position is changed by copying.

Example of using wildcards

Forward, partial, and backward matching can also be achieved using wildcards.


Example of retrieval by setting a numerical threshold

The following example searches for the maximum value of Amount in a range where Price is between 150 and 300.


Comparison operators can be specified for search conditions, such as above or below a specific number.

Comparison operators must be enclosed in double-cotation marks and treated as character strings.


If you specify the criteria as a range of cells, it will be a Spill.

Also, Spill when using the BYROW or BYCOL function.

Row direction
Column direction



Maximum/minimum values for multiple values or cell ranges.

MINHighGet the smallest number.
MAXHighGet the largest number.
MINAMiddleGet the minimum value, including spaces and boolean values.
MAXAMiddleGet the maximum value, including spaces and boolean values.
MINIFSHighGet the minimum value in a cell that matches multiple criteria.
MAXIFSHighGet the maximum value in a cell that matches multiple criteria.

Microsoft Excel Functions Statistical