How To Use Max IF To Observe Maximum Values in Excel (MAXIFS Explained)

Find Maximum/Minimum Values Specified By Sure Criteria

Written past co-founder Kasper Langmann, Microsoft Role Specialist.

Microsoft recently released ii new functions, 'MAXIFS' and 'MINIFS', that will forever modify the way you find minimum and maximum values in a range of cells (based on specific criteria).
maxifs minifs visualized

The fashion it used to be:

If nosotros ever needed to find a maximum or minimum value in a range of cells based on specific criteria, nosotros had to create an assortment formula combining the 'MAX' (or 'MIN') function with an 'IF' part component.

For starters (and clearly), this meant nosotros had to know how to use ii different functions together and it too meant we needed to understand how to create an array formula.

While neither of these is too terribly difficult for nigh Excel users, they are relatively complex for a new or casual user.

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a unlike version? No problem, you can still follow the exact aforementioned steps.

"The 'MAX' and 'MIN' functions in and of themselves find the maximum or minimum value in the range of cells selected"
Kasper Langmann, Co-founder of Spreadsheeto

So whether we select a literal list of values like '=MAX(1,ii,3,4,5)' or a range of jail cell references containing values similar '=MAX(A2:A6)', the upshot is the same and no other criteria may exist used to filter the results.

criteriaYet, we tin can modify this by using the 'IF' function to impose some criteria on the range being analyzed by the 'MAX' (or 'MIN') function and deed equally a filter.

This would look something like '=MIN(IF(A2:A6 > 2,A2:A6))' where nosotros are imposing the criteria that we want the minimum value in the range A2 through A6 greater than ii.

However, this just happens to be an array formula since we are working with a range of cells upon which we are setting criteria.

"Anytime nosotros piece of work with an array formula we much press 'Ctrl + Shift + Enter' instead of the usual 'Enter' in order for the formula to work correctly."
Kasper Langmann, Co-founder of Spreadsheeto

All the same, we sometimes forget to press 'Ctrl + Shift + Enter'  and this quickly leads to frustration – and to exist a flake honest, seems a footling outdated (which is why Microsoft created MAXIFS and MINIFS that yous're going to learn well-nigh in simply a fleck…)

The effigy below illustrates the previous explanations of the 'MAX' and 'MIN' functions using the range A2:A6 for our values.

The result of the formulas are in their respective cells in column C and the actual formula view in column D.

"Observe that the formula for 'MIN' is enclosed in curly brackets which indicates this is an assortment formula and is the event of pressing 'Ctrl + Shift + Enter'"
Kasper Langmann, Co-founder of Spreadsheeto

Since nosotros chose our criteria to be values greater than two, naturally our result is three.

Be aware however, if nosotros printing 'Enter' once we blazon in this formula, it volition not function properly and we volition non get the desired results.

Enter 'MINIFS' and 'MAXIFS'

So, we've just reviewed the almost common way to find minimum or maximum values in a range based on criteria to further drill down into our data.

Let'south turn our attention to a couple of the newest additions to the Excel arsenal of functions that amend accommodates these scenarios: 'MINIFS' and 'MAXIFS'

"The 'MINIFS' and 'MAXIFS' functions at present provide ways to perform "filtered" versions of 'MIN' and 'MAX' within the blueprint of the functions themselves."
Kasper Langmann, Co-founder of Spreadsheeto

Let's breakdown the syntax of 'MINFS' and MAXIFS'. This is all the elements that must be put into the function for it to return our desired results.

The syntax of 'MINFS' and MAXIFS'

'MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)'

one:Our first parameter is 'min_range' which is simply the range from which we are seeking a minimum value. Clearly, this parameter is required.

2: Second, we have 'criteria_range1' which is the range of cells that we will impose our criteria (filter, if you will). This tin can be the same as the 'min_range' or information technology can be another range. However, i matter to notation is that 'criteria_range1' must be the same size and shape as the 'min_range'. This parameter is also required.

iii: The tertiary parameter of the 'MINIFS' role is  the required 'criteria1' which is the criteria that we want to impose upon the 'criteria_range'. This parameter can be a number, an expression or text that tells the function which cells within our 'min_range' to evaluate.

Please notice, that all of these parameters likewise apply to 'MAXIFS'.

We could cease there since those are the only required parameters.

"What makes these functions even more useful and flexible is that they allow for an boosted amount of the 'criteria_range' and 'criteria' parameters in order to "drill downwards" into the information as far equally the state of affairs requires."
Kasper Langmann, Co-founder of Spreadsheeto

Only but similar with 'criteria_range1', farther 'criteria_range' selections must be the same shape and size as the 'min_range' (or 'max_range') in lodge for the function to work properly. Otherwise, it will render the '#VALUE' error.

How to use 'MINFS' and MAXIFS' in a real scenario

Let's take a look at an case where nosotros volition seek the minimum and maximum of a range of data within a larger range.

Follow along past downloading our sample file right beneath!

BONUS: Download the MAXIFS and MINIFS Exercise Workbook File to go forth with this post.

Specifically, we volition seek the minimum and maximum scores within each course range in the following tabular array.

In this scenario, nosotros will move beyond the limitations of the former 'MAX' and 'MIN' functions and leverage the power of 'MAXIFS' and 'MINIFS'.

We are going to use the new functions to detect the minimum and maximum from column A based on criteria in cavalcade B.

"This is a small information ready, just consider nosotros are looking at a data prepare of hundreds or more rows then the value of these functions actually go apparent!"
Kasper Langmann, Co-founder of Spreadsheeto

So allow'south say nosotros would similar to know the maximum and minimum values inside a specific letter of the alphabet form range.

Essentially, what we are looking to exercise is to filter our data based on a letter grade value in the range of data in column B.

Then, with the data filtered, nosotros seek the minimum and maximum values in cavalcade A.

That said, our 'min_range' for the 'MINIFS' role as well every bit the 'max_range' for our 'MAXIFS' function will be the same: A2:A13.

Furthermore, our 'criteria_range1' for both will also be the same, B2:B13.

Our worksheet has a jail cell, D8, where we can input our 'criteria1' parameter for our functions, and so we volition apply that prison cell reference for that argument in both functions.

Obviously, the simply departure between the two formulas in this case is the function itself.

"In the post-obit case, we take chosen the grade B as our filter for finding the minimum and maximum values in column A and our formulas render 84 and 88, respectively."
Kasper Langmann, Co-founder of Spreadsheeto

If we change the letter grade that is our 'criteria1' parameter to A, and so our formulas will return our maximum and minimum grade values:

The previous two examples scratch the surface of what 'MINIFS' and 'MAXIFS' are capable of past making utilize of their ability to drill into data using the criteria parameters.

"Now let's wait at an example that's a bit more complex! "
Kasper Langmann, Co-founder of Spreadsheeto

We will consider a table containing baseball histrion stats that has league, team and position information included for each player.

Nosotros will commencement simple with this data prepare past pulling maximum and minimum statistics such as batting average.

First, allow'due south review how to detect the maximum and minimum values of 1 information betoken, batting average ('AVG' in cavalcade E).

Again, this is every bit elementary equally using the 'MIN' and 'MAX' functions like below.

In this case, all we are doing is looking for the minimum and maximum value for 'AVG' across the entire data set up.

"In the following examples we volition now begin to drill downwards into the data which volition crave u.s.a. to turn to 'MINIFS' and 'MAXIFS' in social club to insert criteria for filtering the data on things like, league, squad, and position."
Kasper Langmann, Co-founder of Spreadsheeto

Where our outset example gave u.s.a. the overall minimum and maximum for 'AVG' in our data set, this side by side example makes use of the 'LEAGUE' column to filter our minimum and maximum results based on two different values, either "American" or "National".

What we have done now is used the C column ($C$ii:$C$117), which is the 'LEAGUE' cavalcade, equally our 'criteria_range1' parameter and so set the 'criteria1' parameter to the cell reference V11.

This cell contains our choice of "American" or "National" and will now filter the data from which the 'MINIFS' and 'MAXIFS' will return a value.

A quick annotation about locked cell references

Detect that our cell references for our ranges have a '$' sometimes preceding the column or row.

This simply locks the cell references for easier copy and paste to other locations inside the worksheet while ensuring those references stay the same.

Creating a locked cell reference can be done manually by actually typing the '$' character earlier the cavalcade or row reference, or we can simply press 'F4' with the jail cell reference highlighted.

Furthermore, nosotros tin can lock both column and row, or just row or column. It all depends on the need at hand.

Adding a 2nd filter to our 'MAXIFS' and 'MINIFS'

Now nosotros want to take our current scenario a pace further and filter the data by team.

Our worksheet table for this example is ready to filter first on 'LEAGUE' and then on any 'Squad' inside that league.

Notation that dissimilar teams overall are a part of the two different leagues, so if nosotros choose "National" for league, our choices for team volition exist limited to those that are within the "National" league.

And then, still looking at 'AVG' as our stat, nosotros accept now filtered the appointment on "American" every bit before, just now we are filtering the data farther on just "KC" for our squad.

This ways nosotros have simply added a couple of new arguments to our 'MINIFS' and 'MAXIFS' formulas.

Nosotros accept now added the range $B$2:$B$117 equally 'criteria_range2' and V19 equally 'criteria2'.

These are the column for 'TEAM' and the jail cell reference for our team pick, respectively.

Now our minimum and maximum value results for 'AVG' are limited to team "KC" which is express to the "American" league.

"We can take it even further by building on the previous case and adding one more than filter, 'POS' or player position."
Kasper Langmann, Co-founder of Spreadsheeto

Essentially, we will at present drill down to minimum and maximum batting average values at the team level based on players that play a particular position similar outfield or infield.

All we accept to do is add together a couple of new arguments to what we already have!

These volition simply be 'criteria_range3' and 'criteria3'.

Since we are filtering on 'POS' our 'criteria_range3' volition be the range $D$2:$D$117 since column D is our 'POS' cavalcade.

Allow's take a expect at the results get-go.

Note that our new parameter for position is in cell V29.

For this example, we now take the minimum and maximum values for batting boilerplate for merely "IF" (infielders) on team "KC" in the "American" league.

So the lowest batting boilerplate among infielders with Kansas City is .256 while the highest is .337.

Observe in the formulas beneath the additional arguments for our 'POS' data range and the cell reference, V29, for the criteria to filter that range on.

"This is a not bad example of how 'MAXIFS' and 'MINIFS' can provide a globe of flexibility in filtering data by which to detect the maximum and minimum values."
Kasper Langmann, Co-founder of Spreadsheeto

Where this was once a fairly complicated and cumbersome prospect using the old methods, Excel at present provides a great bargain of power now that we tin expand the ability to filter data simply and elegantly.