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.