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!
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:
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)
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.
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.
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:
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.
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:
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:
In situation when you need to evaluate numeric criteria, use logical operators such as:
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:
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:
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:
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)
To evaluate a condition that contains a specific text or character, include one of the following wildcard character in your criteria:
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:
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")
=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))
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")
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:
Another way is to use a MAX IF formula with OR logic.
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:
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!
Excel MAXIFS formula examples (.xlsx file)
Table of contents