MAXIFS

Excel Functions

Get maximum value with criteria

Syntax

EXCEL

=MAXIFS(max_range, range1, criteria1, [range2], [criteria2], ...)

Arguments

Parameter	Description
`max_range`	Range of values used to determine maximum.
`range1`	The first range to evaluate.
`criteria1`	The criteria to use on range1.
`range2`	[optional] The second range to evaluate.
`criteria2`	[optional] The criteria to use on range2.

Return Value

Maximum value

Details

The MAXIFS function returns the largest numeric value in cells that meet multiple conditions, referred to as criteria. Each condition is provided with a separate range and criteria. To define criteria, MAXIFS supports various logical operators (>,<,<>,=) and wildcards (*,?,~). The syntax used to apply criteria in MAXIFS is a bit tricky because it is unusual in Excel. See below for details. The syntax for the MAXIFS function depends on the criteria being evaluated. Each condition is provided with a separate range and criteria. The generic syntax for MAXIFS looks like this: The MAXIFS function takes three required arguments: max_range, range1, and criteria1. With these three arguments, MAXIFS returns the maximum number in max_range where corresponding cells in range1 meet the condition set b

Examples

Syntax

The syntax for the MAXIFS function depends on the criteria being evaluated. Each condition is provided with a separate range and criteria. The generic

EXCEL

=MAXIFS(max_range,range1,criteria1) // 1 condition
=MAXIFS(max_range,range1,criteria1,range2,criteria2) // 2 conditions

Basic Example

In the worksheet shown above, the formulas in G5 and G6 are:

EXCEL

=MAXIFS(D5:D16,C5:C16,"F") // returns 93
=MAXIFS(D5:D16,C5:C16,"M") // returns 83

Two criteria

The formulas in H5:I6 are:

EXCEL

H5=MAXIFS(D5:D16,C5:C16,"F",E5:E16,"A") // returns 93
I5=MAXIFS(D5:D16,C5:C16,"F",E5:E16,"B") // returns 85
H6=MAXIFS(D5:D16,C5:C16,"M",E5:E16,"A") // returns 83
I6=MAXIFS(D5:D16,C5:C16,"M",E5:E16,"B") // returns 79

Other criteria

To return the maximum value in A1:A100 when cells in B1:B100 are greater than 50:

EXCEL

=MAXIFS(A1:A100,B1:B100,">50")

Other criteria

To get the maximum value in A1:A100 when cells in B1:B100 are less than or equal to 100, and cells in C1:C100 are greater than zero:

EXCEL

=MAXIFS(A1:A100,B1:B100,"<=100",C1:C100,">0")

Not equal to

To construct "not equal to" criteria, use the "" operator surrounded by double quotes (""). For example, to return the maximum value in A1:A100 when

EXCEL

=MAXIFS(A1:A100,B1:B100,"<>red")

MAXIFS

Syntax

Arguments

Return Value

Details

Examples

See Also