# Excel MAXIFS Function

The Excel MAXIFS function returns the largest numeric value that meets one or more criteria in a range of values. MAXIFS can be used with criteria based on dates, numbers, text, and other conditions. MAXIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

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

The MAXIFS function returns the largest numeric value that meets one or more supplied criteria. The MAXIFS function can apply criteria to dates, numbers, and text. MAXIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

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 by *criteria1*. Additional conditions are applied using range/criteria pairs. The second condition is defined by *range2* and *criteria2*, the third condition is *range3* and *criteria3*, and so on. MAXIFS can handle up to 126 range/criteria pairs.

Each criteria range supplied must be the same size as the *max_range*, or MAXIFS will return a #VALUE! error. If no cells match criteria, MAXIFS will return zero (0). Note that MAXIFS will automatically ignore empty cells in *max_range* that meet criteria.

MAXIFS is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different. MAXIFS *requires* a cell range for range arguments; you can't use an array.

MAXIFS is a newer function, available in Excel 365 and Excel 2019. In earlier versions of Excel you can use an array formula based on MAX and IF to find maximum values with criteria.

### Examples

In the example shown, the formulas in G5 and G6 are:

In the first formula, MAXIFS returns the maximum value in D5:D16 where C5:C16 is equal to "F" (93). In the second formula, MAXIFS returns the maximum value in D5:D16 where C5:C16 is equal to "M" (83).

### Two criteria

In the example below, the MAXIFS function is used with two criteria, one for Gender and one for Group. Note conditions are added in range/criteria pairs. The range E5:E16 is paired with the condition "B".

The formulas in H5:I6 are:

### Other criteria

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

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

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:

=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 cells in B1:B100 are not equal to "red":

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

### Value from another cell

When using a value from another cell in a condition, the cell reference must be concatenated to the operator. For example, to return the maximum value in A1:A100 when cells in B1:B100 are greater than the value in C1:

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

Notice the greater than operator (>) is enclosed in quotes (""), but the cell reference (C1) is not.

### Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in *criteria*. A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters. For example, to return the maximum value in A1:A100 when cells in B1:B100 begin with "a":

=MAXIFS(A1:A100,B1:B100,"a*")

The tilde (~) is an escape character to allow you to find literal wildcards. For example, to match a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

### Notes

- Conditions are applied using range/criteria pairs.
- MAXIFS will return a #VALUE error if any criteria range is not the same size as
*max_range.* - If no criteria match, MAXIFS will return zero (0).
- MAXIFS ignores empty cells that meet criteria.

## Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.