Skip to content

Max Min and If Function

If you are an avid reader of the Spreadsheet Life blog, then you are sure to have seen my post about Four Simple Math Functions.

If you missed it, don’t worry! You can always check it out later. Nonetheless, I introduced a few simple math functions, including the MAX and MIN functions.

The MAX function finds the greatest value in a dataset, and the MIN function finds the smallest value. That makes these functions pretty useful.

But what if you want to find the largest or smallest value based on a condition or several criteria? What do you do then?

In this case, you would use the MAXIFS and MINIFS functions instead.

The MAXIFS Function

The MAXIFS function combines the power of the MAX function with the IF function.

The MAX function looks at a set of values and returns the greatest value among them. On the other hand, the MAXIFS function only looks at the values that meet the conditions you specify.

The MAXIFS function then returns the largest value from among them.

For example, let’s say you have the following dataset.

Now let’s say you want to find the largest value, but only from the values that meet the following criteria.

  • Only values that correspond with Blue
  • Only values that are less than 5

The only values that correspond with Blue are 13, and 5.

The only values less than 5 are 123, and 4.

The only values that meet both criteria are 1 and 3. Therefore, the MAXIFS function will only look at these values and return a value of 3.

How to Use the MAXIFS Function

The MAXIFS function requires three arguments but can accept up to 126 range/criteria argument pairs.

The arguments are as follows.

  • Range (required)
  • Criteria_Range1 (required)
  • Criteria1 (required)
  • Criteria Range2
  • Criteria2
  • Etc.

The first argument is the actual range of cells in which the maximum is determined.

=MAXIFS(range, criteria_range1, criteria1, criteria_range2, criteria2, etc.)

The second argument is the set of cells that tested against the first criterion, and the third argument is that criterion.

=MAXIFS(range, criteria_range1, criteria1, criteria_range2, criteria2, etc.)

Additional range/criteria pairs work the same way.

In the example shown above, the MAXIFS function:

  • References the cells containing numbers as the first argument
  • References the cells containing colors in the second argument
  • Specifies the color in the third argument
  • References the cells containing numbers as the fourth argument
  • Specifies the second criterion in the fifth argument

The MINIFS Function

The MINIFS function works just like the MAXIFS function but with one major difference.

Instead of returning the largest value among the values that meet the criteria, it returns the smallest value.

For instance, if you replace the MAXIFS function in the previous example with the MINIFS function, then you get the number 1 instead of 3.

The only values that correspond with Blue are 13, and 5.

The only values less than 5 are 123, and 4.

The only values that meet both criteria are 1 and 3. Therefore, the MINIFS function will only look at these values and return a value of 1.

How to Use the MINIFS Function

The MINIFS function works just like the MAXIFS function and requires three arguments but can accept up to 126 range/criteria argument pairs.

If you understand how to use the MAXIFS function, then you’ve got the MINIFS function down too. Just don’t forget that it returns the smallest value instead of the greatest value.

And that’s how you can find the maximum and minimum among a set of values that meet a set of criteria.

It’s also important to note that both functions are available in Google Sheets and Excel versions 2019 or later.

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top