Excel MAXIFS function - get largest value based on multiple criteria

Svetlana Cheusheva by , updated on

The tutorial shows how to use the MAXIFS function in Excel to get the maximum value with conditions.

Traditionally, when you ever needed to find the highest value with conditions in Excel, you had to build your own MAX IF formula. While not a big deal for experienced users, that might present certain difficulties for novices because, firstly, you should remember the formula's syntax and, secondly, you need to know how to work with array formulas. Luckily, Microsoft has recently introduced a new function that lets us do conditional max an easy way!

Excel MAXIFS function

The MAXIFS function returns the largest numeric value in the specified range based on one or more criteria.

The syntax of the MAXIFS function is as follows:

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

Where:

  • Max_range (required) - the range of cells where you want to find the maximum value.
  • Criteria_range1 (required) - the first range to evaluate with criteria1.
  • Criteria1 - the condition to use on the first range. It can be represented by a number, text or expression.
  • Criteria_range2 / criteria2, …(optional) - additional ranges and their related criteria. Up to 126 range/criteria pairs are supported.

This MAXIFS function is available in Excel 2019, Excel 2021, and Excel for Microsoft 365 on Windows and Mac.

As an example, let's find the tallest football player in our local school. Assuming the students' heights are in cells D2:D11 (max_range) and sports are in B2:B11 (criteria_range1), use the word "football" as criteria1, and you will get this formula:

=MAXIFS(D2:D11, B2:B11, "football")

To make the formula more versatile, you can input the target sport in some cell (say, G1) and include the cell reference in the criteria1 argument:

=MAXIFS(D2:D11, B2:B11, G1)
MAXIFS function in Excel

Note. The max_range and criteria_range arguments must be of the same size and shape, i.e. contain the equal number of rows and columns, otherwise the #VALUE! error is returned.

How to use MAXIFS function in Excel - formula examples

As you have just seen, the Excel MAXIFS is quite straightforward and easy to use. However, it does have a few little nuances that make a big difference. In the below examples, we will try to make the most of conditional max in Excel.

Find max value based on multiple criteria

In the first part of this tutorial, we created a MAXIFS formula in its simplest form to get the max value based on one condition. Now, we are going to take that example further and evaluate two different criteria.

Supposing, you want to find the tallest basketball player in junior school. To have it done, define the following arguments:

  • Max_range - a range of cells containing heights - D2:D11.
  • Criteria_range1 - a range of cells containing sports - B2:B11.
  • Criteria1 - "basketball", which is input in cell G1.
  • Criteria_range2 - a range of cells defining the school type - C2:C11.
  • Criteria2 - "junior", which is input in cell G2.

Putting the arguments together, we get these formulas:

With "hardcoded" criteria:

=MAXIFS(D2:D11, B2:B11, "basketball", C2:C11, "junior")

With criteria in predefined cells:

=MAXIFS(D2:D11, B2:B11, G1, C2:C11, G2)

Please notice that the MAXIFS function in Excel is case-insensitive, so you needn't worry about the letter case in your criteria.
Finding the max value based on multiple criteria

In case you plan to use your formula on multiple cells, be sure to lock all the ranges with absolute cell references, like this:

=MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2)

This will ensure that the formula copies to other cells correctly - the criteria references change based on the relative position of the cell where the formula is copied while the ranges remain unchanged:
Excel MAXIFS formula with multiple criteria

As an extra bonus, I will show you a quick way to extract a value from another cell that is associated with the max value. In our case, that will be the name of the tallest person. For this, we will be using the classic INDEX MATCH formula and nest MAXIFS in the first argument of MATCH as the lookup value:

=INDEX($A$2:$A$11, MATCH(MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2), $D$2:$D$11, 0))

The formula tells us that the name of the tallest basketball player in junior school is Liam:
Extract a value from another cell that is associated with the max value.

Excel MAXIFS with logical operators

In situation when you need to evaluate numeric criteria, use logical operators such as:

  • greater than (>)
  • less than (<)
  • greater than or equal to (>=)
  • less than or equal to (<=)
  • not equal to (<>)

The "equal to" operator (=) can be omitted in most cases.

Usually, choosing an operator is not a problem, the trickiest part is to build criteria with the correct syntax. Here's how:

  • A logical operator followed by a number or text must be enclosed in double quotes like ">=14" or "<>running".
  • In case of a cell reference or another function, use the quotes to begin a string and an ampersand to concatenate the reference and finish the string off, e.g. ">"&B1 or "<"&TODAY().

To see how it works in practice, let's add the Age column (column C) to our sample table and find the maximum height among the boys aged between 13 and 14. This can be done with the following criteria:

Criteria1: ">=13"

Criteria2: "<=14"

Because we compare the numbers in the same column, criteria_range in both cases is the same (C2:C11):

=MAXIFS(D2:D11, C2:C11, ">=13", C2:C11, "<=14")

If you do not want to hardcode the criteria in the formula, input them in separate cells (e.g. G1 and H1) and use the following syntax:

=MAXIFS(D2:D11, C2:C11, ">="&G1, C2:C11, "<="&H1)

The screenshot below shows the result:
Find the max value with greater than and less than criteria

Aside from numbers, logical operators can also work with text criteria. In particular, the "not equal to" operator comes in handy when you wish to exclude something from your calculations. For example, to find the tallest student in all sports excluding volleyball, use the following formula:

=MAXIFS(D2:D11, B2:B11, "<>volleyball")

Or this one, where G1 is the excluded sport:

=MAXIFS(D2:D11, B2:B11, "<>"&G1)
A MAXIFS formula with the not equal to condition

MAXIFS formulas with wildcard characters (partial match)

To evaluate a condition that contains a specific text or character, include one of the following wildcard character in your criteria:

  • Question mark (?) to match any single character.
  • Asterisk (*) to match any sequence of characters.

For this example, let's find out the tallest guy in game sports. Because the names of all game sports in our dataset end with the word "ball", we include this word in the criteria and use an asterisk to match any previous characters:

=MAXIFS(D2:D11, B2:B11, "*ball")

You can also type "ball" in some cell, e.g. G1, and concatenate the wildcard character with the cell reference:

=MAXIFS(D2:D11, B2:B11, "*"&G1)

The result will look as follows:
A MAXIFS formula with wildcard character

Get max value within a date range

Because dates are stored as serial numbers in the internal Excel system, you work with the dates criteria in the same manner as you work with numbers.

To illustrate this, we will replace the Age column with Date of Birth and try to work out the max height among the boys born in a particular year, say in 2004. To accomplish this task, we need to "filter" the birth dates that are greater than or equal to 1-Jan-2004 and less than or equal to 31-Dec-2004.

When building your criteria, it is important that you provide the dates in the format that Excel can understand:

=MAXIFS(D2:D11, C2:C11, ">=1-Jan-2004", C2:C11, "<=31-Dec-2004")

Or

=MAXIFS(D2:D11, C2:C11, ">=1/1/2004", C2:C11, "<=12/31/2004")

To prevent misinterpretation, it makes sense to utilize the DATE function:

=MAXIFS(D2:D11, C2:C11, ">="&DATE(2004,1,1), C2:C11, "<="&DATE(2004,12,31))

For this example, we will type the target year in G1, and then use the DATE function to supply the dates:

=MAXIFS(D2:D11, C2:C11, ">="&DATE(G1,1,1), C2:C11, "<="&DATE(G1,12,31))
Conditional max for dates

Note. Unlike numbers, dates should be enclosed in quotation marks when used in the criteria on their own. For example:

=MAXIFS(D2:D11, C2:C11, "10/5/2005")

Find maximum value based on multiple criteria with OR logic

The Excel MAXIFS function is designed to test the conditions with the AND logic - i.e. it processes only those numbers in max_range for which all the criteria are TRUE. In some situations, however, you may need to evaluate the conditions with the OR logic - i.e. process all the numbers for which any of the specified criteria is TRUE.

To make things easier to understand, please consider the following example. Supposing you want to find the maximin height of the guys who play either basketball or football. How would you do that? Using "basketball" as criteria1 and as "football" criteria2 won't work, because Excel would assume that both criteria should evaluate to TRUE.

The solution is to make 2 separate MAXIFS formulas, one per each sport, and then use the good old MAX function to return a higher number:

=MAX(MAXIFS(C2:C11, B2:B11, "basketball"), MAXIFS(C2:C11, B2:B11, "football"))

The screenshot below shows this formula but with the criteria in predefined input cells, F1 and H1:
Find the max value based on multiple criteria with OR logic

Another way is to use a MAX IF formula with OR logic.

7 things to remember about Excel MAXIFS

Below you will find a few remarks that will help to improve your formulas and avoid common errors. Some of these observations have already been discussed as tips and notes in our examples, but it might be helpful to get a short summary of what you've already learned:

  1. The MAXIFS function in Excel can get the highest value based on one or multiple criteria.
  2. By default, Excel MAXIFS works with the AND logic, i.e. returns the maximum number that meets all of the specified conditions.
  3. For the function to work, the max range and criteria ranges must have the same size and shape.
  4. The SUMIF function is case-insensitive, i.e. it does not recognize the letter case in text criteria.
  5. When writing a MAXIFS formula for multiple cells, remember to lock the ranges with absolute cell references for the formula to copy correctly.
  6. Mind the syntax of your criteria! Here are the main rules:
    • When used on their own, text and dates should be enclosed in quotation marks, numbers and cell references should not.
    • When a number, date or text is used with a logical operator, the whole expression must be enclosed in double quotes like ">=10"; cell references and other functions must be concatenated by using an ampersand like ">"&G1.
  7. MAXIFS is only available in Excel 2019 and Excel for Office 365. In earlier versions, this function is not be available.

That's how you can find the maximum value in Excel with conditions. I thank you for reading and hope to see you on our blog soon!

Download practice workbook:

Excel MAXIFS formula examples (.xlsx file)

Table of contents