How To Use MAXIFS and MINIFS To Find Maximum/Minimum Values Specified By Certain Criteria

How to Use Excel’s New Functions:
MAXIFS and MINIFS

Find Maximum/Minimum Values Specified By Certain Criteria

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

The way it used to be:

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

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

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

“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,2,3,4,5)’ or a range of cell references containing values like ‘=MAX(A2:A6)’, the outcome is the same and no other criteria may be used to filter the results.

criteriaHowever, we can change this by using the ‘IF’ function to impose some criteria on the range being analyzed by the ‘MAX’ (or ‘MIN’) function and act as a filter.

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

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 we 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

However, we sometimes forget to press ‘Ctrl + Shift + Enter’  and this quickly leads to frustration – and to be a bit honest, seems a little outdated (which is why Microsoft created MAXIFS and MINIFS that you’re going to learn about in just a bit…)

The figure 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.

“Notice that the formula for ‘MIN’ is enclosed in curly brackets which indicates this is an array formula and is the result of pressing ‘Ctrl + Shift + Enter’”
Kasper Langmann, Co-founder of Spreadsheeto

Since we chose our criteria to be values greater than 2, naturally our result is 3.

Be aware however, if we press ‘Enter’ once we type in this formula, it will not function properly and we will not get the desired results.

Enter ‘MINIFS’ and ‘MAXIFS’

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

Let’s turn our attention to a couple of the newest additions to the Excel arsenal of functions that better accommodates these scenarios: ‘MINIFS’ and ‘MAXIFS’ 

“The ‘MINIFS’ and ‘MAXIFS’ functions now provide ways to perform “filtered” versions of ‘MIN’ and ‘MAX’ within the design 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], …)’

 1: 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 can be the same as the ‘min_range’ or it can be another range. However, one thing to note is that ‘criteria_range1’ must be the same size and shape as the ‘min_range’. This parameter is also required.

3: The third parameter of the ‘MINIFS’ function 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 also apply to ‘MAXIFS’.

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

“What makes these functions even more useful and flexible is that they allow for an additional amount of the ‘criteria_range’ and ‘criteria’ parameters in order to “drill down” into the data as far as the situation requires.”
Kasper Langmann, Co-founder of Spreadsheeto

But just like with ‘criteria_range1’, further ‘criteria_range’ selections must be the same shape and size as the ‘min_range’ (or ‘max_range’) in order for the function to work properly. Otherwise, it will return the ‘#VALUE’ error.

How to use ‘MINFS’ and MAXIFS’ in a real scenario

Let’s take a look at an example where we will seek the minimum and maximum of a range of data within a larger range.

Follow along by downloading our sample file right below!

Click to Download This Tutorial’s FREE Sample File

Specifically, we will seek the minimum and maximum scores within each grade range in the following table.

In this scenario, we will move beyond the limitations of the old ‘MAX’ and ‘MIN’ functions and leverage the power of ‘MAXIFS’ and ‘MINIFS’.

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

“This is a small data set, but consider we are looking at a data set of hundreds or more rows and then the value of these functions really become apparent!”
Kasper Langmann, Co-founder of Spreadsheeto

So let’s say we would like to know the maximum and minimum values within a specific letter grade range.

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

Then, with the data filtered, we seek the minimum and maximum values in column A.

That said, our ‘min_range’ for the ‘MINIFS’ function as well as 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 cell, D8, where we can input our ‘criteria1’ parameter for our functions, so we will use that cell reference for that argument in both functions.

Obviously, the only difference between the two formulas in this case is the function itself. 

“In the following example, we have chosen the grade B as our filter for finding the minimum and maximum values in column A and our formulas return 84 and 88, respectively.”
Kasper Langmann, Co-founder of Spreadsheeto

If we change the letter grade that is our ‘criteria1’ parameter to A, then 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 by making use of their ability to drill into data using the criteria parameters.

“Now let’s look at an example that’s a bit more complex! ”
Kasper Langmann, Co-founder of Spreadsheeto

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

We will start simple with this data set by pulling maximum and minimum statistics such as batting average.

First, let’s review how to find the maximum and minimum values of one data point, batting average (‘AVG’ in column E).

Again, this is as simple as 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.

“In the following examples we will now begin to drill down into the data which will require us to turn to ‘MINIFS’ and ‘MAXIFS’ in order to insert criteria for filtering the data on things like, league, team, and position.”
Kasper Langmann, Co-founder of Spreadsheeto

Where our first example gave us the overall minimum and maximum for ‘AVG’ in our data set, this next 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$2:$C$117), which is the ‘LEAGUE’ column, as our ‘criteria_range1’ parameter and then 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 note about locked cell references

Notice 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 within the worksheet while ensuring those references stay the same.

Creating a locked cell reference can be done manually by actually typing the ‘$’ character before the column or row reference, or we can simply press ‘F4’ with the cell reference highlighted.

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

Adding a second filter to our ‘MAXIFS’ and ‘MINIFS’

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

Our worksheet table for this example is set up to filter first on ‘LEAGUE’ and then on any ‘TEAM’ within that league.

Note that different teams overall are a part of the two different leagues, so if we choose “National” for league, our choices for team will be limited to those that are within the “National” league.

So, still looking at ‘AVG’ as our stat, we have now filtered the date on “American” as before, but now we are filtering the data further on just “KC” for our team.

This means we have simply added a couple of new arguments to our ‘MINIFS’ and ‘MAXIFS’ formulas.

We have now added the range $B$2:$B$117 as ‘criteria_range2’ and V19 as ‘criteria2’.

These are the column for ‘TEAM’ and the cell reference for our team selection, respectively.

Now our minimum and maximum value results for ‘AVG’ are limited to team “KC” which is limited to the “American” league.

“We can take it even further by building on the previous example and adding one more filter, ‘POS’ or player position.”
Kasper Langmann, Co-founder of Spreadsheeto

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

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

These will simply be ‘criteria_range3’ and ‘criteria3’.

Since we are filtering on ‘POS’ our ‘criteria_range3’ will be the range $D$2:$D$117 since column D is our ‘POS’ column.

Let’s take a look at the results first.

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

For this example, we now have the minimum and maximum values for batting average for only “IF” (infielders) on team “KC” in the “American” league.

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

Notice in the formulas below the additional arguments for our ‘POS’ data range and the cell reference, V29, for the criteria to filter that range on.

“This is a great example of how ‘MAXIFS’ and ‘MINIFS’ can provide a world of flexibility in filtering data by which to find 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 now provides a great deal of power now that we can expand the ability to filter data simply and elegantly.

CLICK HERE to try our free Excel training.

2017-02-28T08:45:48+00:00

Send this to friend