How to use the Excel MAXIFS function

Summary

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.

Purpose

Get maximum value with criteria

Return value

Maximum value

Syntax

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

Arguments

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.

Usage notes

MAXIFS is a new function, available in Office 365 and Excel 2019.

The MAXIFS function can be used to find a maximum value in data after applying one or more criteria. Conditions are applied using range/criteria pairs. MAXIFS can handle up to 126 range/criteria pairs. Each criteria range supplied must be the same size as the man_range.

The MAXIFS function can apply criteria to dates, numbers, and text. MAXIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

In earlier versions of Excel you can use an array formula based on MAX and IF to find maximum values with criteria.

Example

In the example shown, the formula in G7 is:

=MAXIFS(D3:D15,C3:C15,"F")

MAXIFS returns the maximum value in D3:D15 where C3:C15 is equal to "F".

Notes

Criteria range(s) must be the same size as the max_range.
MAXIFS will return a #VALUE error if a criteria range is not the same size as the min_range.

Related functions

Excel MINIFS Function

The Excel MINIFS function returns the smallest numeric value that meets one or more criteria in a range of values. MINIFS can be used with criteria based on dates, numbers, text, and other conditions. MINIFS supports logical operators (>,...

Excel MIN Function

The Excel MIN function returns the smallest numeric value in a range of values. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.

Excel MAX Function

The Excel MAX function returns the largest numeric value in a range of values. The MAX function ignores empty cells, the logical values TRUE and FALSE, and text values.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.