MS Excel: How to use the MAXIFS Function (WS)

This Excel tutorial explains how to use the Excel MAXIFS function with syntax and examples.

Description

The Microsoft Excel MAXIFS function returns the largest value in a range, that meets a single or multiple criteria.

The MAXIFS function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the MAXIFS function can be entered as part of a formula in a cell of a worksheet.

Subscribe

---

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the MAXIFS function in Microsoft Excel is:

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

Parameters or Arguments

max_range

The range of cells where you will determine the largest or maximum value.

range1

The range of cells that you want to apply criteria1 against.

criteria1

The criteria used to determine which cells to evaluate as the largest. criteria1 is applied against range1.

range2, ... range_n

Optional. It is the range of cells that you want to apply criteria2, ... criteria_n against. There can be up to 126 ranges.

criteria2, ... criteria_n

Optional. It is used to determine which cells to evaluate as the largest. criteria2 is applied against range2, criteria3 is applied against range3, and so on. There can be up to 126 criteria.

Returns

The MAXIFS function returns a numeric value that represents the largest value in a range of cells, given one or more criteria applied.

Applies To

• Excel for Office 365, Excel 2019

Type of Function

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MAXIFS function examples and explore how to use the MAXIFS function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following MAXIFS examples would return:

=MAXIFS(C2:C10,B2:B10,"Oranges")
Result: 15.99   'Returns the maximum cost for Oranges

=MAXIFS(C2:C10,B2:B10,"Oranges",A2:A10,">=2019")
Result: 13.45   'Returns the maximum cost for Oranges since 2019

=MAXIFS(C2:C10,A2:A10,"2020")
Result: 7.95    'Returns the maximum cost for the year 2020

=MAXIFS(C2:C10,B2:B10,"A*")
Result: 12.50   'Uses the * wildcard to return the maximum cost for all products that start with the letter A

=MAXIFS(C2:C10,B2:B10,"B?nanas")
Result: 10.50   'Uses the ? wildcard to match on a single character, ie: Bananas, Benanas, Binanas, Bonanas, and so on

Using Named Ranges

You can also use a named range in the MAXIFS function. A named range is a descriptive name for a collection of cells or range in a worksheet. If you are unsure of how to setup a named range in your spreadsheet, read our tutorial on Adding a Named Range.

In the next example, we've created two named ranges. The first named range is called Products which refers to the range B2:B10 in Sheet 1 and the second named range is called Costs which refers to the range C2:C10 in Sheet1.

Then we can then use these two named ranges in our Excel formula.

Based on the Excel spreadsheet above, we've replaced the ranges C2:C10 and B2:B10 with the named ranges called Costs and Products as follows:

=MAXIFS(Costs,Products,"Oranges")

This formula will return 15.99 which is the maximum cost for Oranges and is equivalent to the following formula:

=MAXIFS(C2:C10,B2:B10,"Oranges")