The Excel MAXIFS Function

Related Functions:
MINIFS
MAX

Basic Description

The Excel Maxifs function returns the maximum value from a subset of values that are specified according to one or more criteria.

The syntax of the function is:

MAXIFS( max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Where the function arguments are:

max_range-An array of numeric values (or a range of cells containing numeric values), from which you want to return the maximum value if the criteria are satisfied.
criteria_range1-

An array of values (or range of cells containing values) to be tested against criteria1.

(This array must all have the same length as the max_range).
criteria1-The condition to be tested against the values in criteria_range1.
[criteria_range2, criteria2],
[criteria_range3, criteria3], ...
-Optional further arrays of values to be tested and the respective conditions to test.

Notes:


Excel Maxifs Function Examples

The spreadsheet below shows the quarterly sales figures for 3 sales representatives.

The Maxifs function can be used to find the maximum sales figure for any quarter, area or sales rep (or any combination of quarter, area and sales rep).

This is shown in the examples below.


 ABCD
1QuarterAreaSales Rep.Sales
21NorthJeff$223,000
31NorthChris$125,000
41SouthCarol$456,000
52NorthJeff$322,000
62NorthChris$340,000
72SouthCarol$198,000
83NorthJeff$310,000
93NorthChris$250,000
103SouthCarol$460,000
114NorthJeff$261,000
124NorthChris$389,000
134SouthCarol$305,000

Example 1

To find the maximum sales figure during quarter 1:

=MAXIFS( D2:D13, A2:A13, 1 )

which gives the result $456,000.

In this example, the Excel Maxifs function identifies rows where the value in column A is equal to 1, and returns the maximum value from the corresponding values in column D.

I.e. the function finds the maximum of the values $223,000, $125,000 and $456,000 (from cells D2, D3 and D4).


Example 2

Again, using the data spreadsheet above, we can also use the Maxifs function to find the maximum sales figure for "Jeff", during quarters 3 and 4:

=MAXIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

This formula returns the result $310,000.

In this example, the Excel Maxifs function identifies rows where:

and

and returns the maximum of the corresponding values in column D.

I.e. this formula finds the maximum of the values $310,000 and $261,000 (from cells D8 and D11).


See the Microsoft Office website for further details examples of the Excel Maxifs function.


Maxifs Function Errors

If you get an error from the Excel Maxifs function, this is likely to be one of the following:

Common Errors
#VALUE!-Occurs if the supplied max_range and criteria_range arrays do not all have equal length.
#NAME?-Occurs if you are using an older version of Excel (pre-2016), that does not support the Maxifs function.