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
In the example shown, the formula in G7 is:
=MAXIFS(D3:D15,C3:C15,"F")
- Criteria range(s) must be the same size and shape as the max_range.
- The MAXIFS function was introduced in Excel 2016.
- In earlier versions of Excel you can use an array formula based on MAX and IF to find minimum values with criteria.
MAXIFS formula examples
To get "first in, last out" times from timesheet data, you can use the MAXIFS and MINIFS functions, or an array formula as described below. In the example shown, the formula in H6 is:
=MINIFS(times,names,H5,actions,...
To get the maximum value from a set of numbers, use the MAX function.
In the example shown, the formula in cell I6 is:
=MAX(C6:G6)
The MAX function is fully automatic. It accepts one or more arguments, which can be...
To get a maximum value based on criteria, you can use the MAX function together with the IF function in an array formula. In the example shown, the formula in cell G6 is:
{=MAX(IF(names=F6,times))}
Where names is the...
To get the maximum value in numeric data while ignoring all errors, you can use the AGGREGATE function, as explained below. In the example shown, the formula in E5 is:
=AGGREGATE(4,6,values)
where "values" is the...
To get the maximum value in a data set based on more than one criteria, you can use an array formula based on the MAX and IF functions. In the example shown, the formula in I6 is:
{=MAX(IF(color=G6,IF(item=H6,price...
Related functions
Good links
