How to Use MAXIFS and MINIFS Functions in Excel

Microsoft Office continues to enchant its users with its fancy but valuable formulas. There are probably many useful functions in Microsoft Excel that, despite their usefulness, are not used on a daily basis.

The MAXIFS and MINIFS functions in Excel may be one of the least used functions, but their usefulness cannot be overlooked. If you want to understand the methodology, usage and practicality of these two super essential functions in Excel, follow the examples below to explain the usefulness of these two functions.

Prerequisites for MAXIFS and MINIFS functions in Excel

The MAXIFS and MINIFS are two of the most useful functions in Excel, after their counterparts COUNTIF, COUNTIFS, SUMIF, and SUMIFS.

Although the MAXIFS and MINIFS functions calculate the maximum and minimum values ​​over a range of values, you can learn to use the COUNTIF, SUMIF, and their related functions to perform useful arithmetic tasks.

These features are only available in Office 365 and Office 2019; If you’re using an older version of Excel, you’ll need to upgrade to one of these newer versions before you can use it.

practice record

You can use an example dataset to understand the concepts of the MAXIFS and MINIFS functions.

order date city category subcategory sale Benefit
11/08/2016 Kentucky furniture bookshelves 261.96 41.9136
11/08/2016 California furniture chairs 731.94 219,582
12-06-2016 Florida office supplies labels 14.62 6.8714
10/11/2015 Florida furniture tables 957.5775 -383.031
10/11/2015 California office supplies storage 22,368 2.5164
06.09.2014 California furniture furnishings 48.86 14.1694
06.09.2014 California office supplies art 7.28 1.9656
06.09.2014 California technology phones 907.152 90.7152
06.09.2014 California office supplies binder 18,504 5.7825
06.09.2014 California office supplies domestic appliances 114.9 34.47
06.09.2014 California furniture tables 1706.184 85.3092
06.09.2014 North Carolina technology phones 911,424 68.3568
04/15/2017 Washington office supplies paper 15,552 5.4432

syntax structure

The MAXIFS and MINIFS functions in Excel find the maximum and minimum values, respectively, in a range of cells. The syntax for these functions is as follows:

MAXIFS (max_range, criteria_range1, criteria1, ...)
MINIFS (min_range, criteria_range1, criteria1, ...)

Both functions accept the following arguments, respectively:

  • max_range/min_range: Because this is the first argument, you need to define the range you want to evaluate. Think of this value as the end result you are looking for from a list of options.
  • Criteria area1: Define the range as a reference within the formula.
  • Criteria1: Specify the criteria to search for criteria_area1 Pillar.

Optionally, you can add more scopes and criteria arguments to the function, as follows:

  • Criteria_Area2: Define the second range of values ​​to use as a reference in the formula.
  • criteria2: Specify the dependent criteria to search for criteria_area2 Pillar.

Since you are dealing with multiple levels of formulas and their individual arguments, you should always try to understand Excel formulas step-by-step to help you get started with the formula creation process.

How to use MAXIFS and MINIFS functions with numbers in Excel

The MAXIFS and MINIFS functions work with numbers, text, and dates. To illustrate usage with numbers, you can look at win amounts (Column F) in the sample data set.

There are a variety of arguments that you can use when defining the conditions. You can easily choose between a single condition and multiple conditions.

Working with a single condition

To find the maximum profit for a specific subcategory, let’s say tablesuse the formula shown below:

=MAXIFS(F2:F14, D2:D14, "Tables")

The above formula accepts the following arguments:

  • MAXIFS: Excel function to calculate the maximum value based on a condition.
  • F2:F14: The function returns the maximum value based on the condition you specify.
  • D2:D14: The conditional search value is available in this column (tables).
  • Tables: Value to look up within the lookup range.

For similar reasons, you can calculate the minimum win for tables by defining the range and criteria as follows:

=MINIFS(F2:F14, D2:D14, "Tables")

The maximum value is 85.3092 and the minimum value is -383.031.

Working with multiple conditions

There aren’t many variations in the code when working with multiple conditions. Instead of defining a single condition, add another dependent condition to the formula. Use the formulas below to calculate the maximum and minimum retail value for furniture in California.

=MAXIFS(E2:E14, B2:B14, "California", C2:C14, "Furniture")
=MINIFS(E2:E14, B2:B14, "California", C2:C14, "Furniture")

Notice the use of two conditions within the formula. In layman’s terms, Excel filters the data for California and furniture before calculating the maximum and minimum sales values.

The maximum value is 1706.184 while the minimum value is 48.86.

Use logical operators in numbers

Logical operators are used in conjunction with MAXIFS and MINIFS functions to specify additional conditions that you can use to improve the effectiveness of your calculations. The logical operators present in Excel are:

operator meaning
= same
> greater than
< Less than
>= Greater Than Equal
<= Less than equal

Now if you want to use one of these operators to return a value based on a condition, you can use it like this:

=MAXIFS(E2:E14, E2:E14,"<50")
=MINIFS(E2:E14, E2:E14,"<50")

This formula returns the maximum value of 48.86 from the Sales column. The second formula returns the minimum value, which is 7.28.

How to use MAXIFS and MINIFS functions with dates in Excel

If you sometimes need to search for values ​​that fall within a date range, you can easily use the MAXIFS and MINIFS functions. To find a value that falls after a specific date, you can use:

=MAXIFS(E2:E14, A2:A14, ">02-01-2016")
=MINIFS(E2:E14, A2:A14, ">02-01-2016")

As you might have guessed, the first formula returns the value 731.94this is the maximum value between the order dates 02/01/2016 and 04/15/2017. Then the second formula returns the value 14.62since the date conditions are the same as before.

If you want to expand this formula and include a range of dates, you can do it like this:

=MAXIFS(E2:E14, A2:A14, ">02-01-2015", A2:A14, "<02-01-2017")
=MINIFS(E2:E14, A2:A14, ">02-01-2015", A2:A14, "<02-01-2017")

The earlier function returns the value 957.5775while the latter returns the corresponding value of 14.62.

How to use MAXIFS and MINIFS functions with text values ​​in Excel

There might not be much to expect when using the MAXIFS and MINIFS functions with text values. The result is usually a value for a specific condition specified within the formula scope.

Suppose you want to calculate the maximum and minimum sale values ​​for California. To do this, you can enter the city name directly within the formula.

=MAXIFS(E2:E14, B2:B14, "California")
=MINIFS(E2:E14, B2:B14, "California")

When working with text criteria, be sure to match the case of the search value with the original text value. As expected, the highest is in California 1706.184and the minimum value is 7.28.

Working with conditional functions in Excel

Excel provides many conditional formulas, making it a breeze to work with various logical statements. Conditional statements like AVERAGEIF, SUMIF, SUMIFS, and other related functions make Microsoft Excel a delight for data analysts.

Although the concept of these statements is more or less the same, a few differences set them apart from the list of available functions.

Leave a Reply

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