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

03/28/2024

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)`

## Demonstrate

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

`=MAXIFS(\$G\$4:\$G\$11,\$D\$4:\$D\$11,J4,\$H\$4:\$H\$11,TRUE)`

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.

`=MAXIFS(\$B\$2:\$B\$10,\$A\$2:\$A\$10,"Pen*") `
`=MAXIFS(\$B\$2:\$B\$10,\$A\$2:\$A\$10,"*book*") `
`=MAXIFS(\$B\$2:\$B\$10,\$A\$2:\$A\$10,"*C")`

### 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.

`=MAXIFS(G4:G11,E4:E11,">="&N4,E4:E11,"<"&O4)`
`=MAXIFS(G4:G11,E4:E11,">=150",E4:E11,"<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.

## Spill

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

Also, Spill when using the BYROW or BYCOL function.

`=BYROW(A1:C3,LAMBDA(r,MAXIFS(r,r,">=4",r,"<=7")))`
`=BYCOL(A1:C3,LAMBDA(c,MAXIFS(c,c,">=4",c,"<=7")))`

---