The MAXIFS and MINIFS functions in Excel may be one of the least used functions, but their utility cannot be overlooked. If you want to understand the methodology, usage, and practicality of these two uber essential functions in Excel, follow along for some relevant examples explaining the utility of these two functions.
Prerequisites of 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.
Even though the MAXIFS and MINIFS functions calculate the maximum and minimum values in a range of values, you can learn to use the COUNTIF, SUMIF, and their related functions to perform meaningful arithmetic tasks.
These functions are available in Office 365 and Office 2019 only; if you’re using an older version of Excel, you’ll need to update to one of these newer versions, before you can hope to use them.
Practice Dataset
You can use a sample dataset to understand the concepts of MAXIFS and MINIFS functions.
Syntax Structure
The MAXIFS and MINIFS functions in Excel find the maximum and minimum values in a range of cells, respectively. The syntax for these functions is as follows:
Both functions take the following arguments respectively:
max_range/min_range: Since this is the first argument, you must define the range you want to evaluate. Imagine this value as the final result you seek from a list of options. Criteria_range1: Define the range as reference within the formula. Criteria1: Specify the criteria to look for within the criteria_range1 column.
You can optionally add more ranges and criteria arguments to the function as follows:
Criteria_range2: Define the second range of values to use as a reference within the formula. Criteria2: Specify the dependent criteria to look for within the criteria_range2 column.
Since you are dealing with multiple layers of formulas and their individual arguments, you should always try to understand Excel formulas step by step, to ease yourself into the formula building process.
How to Use MAXIFS and MINIFS Functions With Numbers in Excel
The MAXIFS and MINIFS functions works with numbers, text, and date values. To illustrate the usage with numbers, you can consider the profit amounts (Column F) in the sample data set.
There are a variety of arguments 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 sub-category, say Tables, use the formula shown below:
The above formula takes the following arguments:
MAXIFS: Excel function to calculate the maximum value based on a condition. F2:F14: The function returns the maximum value basis to the condition you specify. D2:D14: The conditional lookup value is available within this column (Tables). Tables: Value to look up within the lookup range.
On similar grounds, you can calculate the minimum profit for Tables by defining the range and criteria as follows:
The maximum value is 85.3092 and the minimum value is -383.031.
Working With Multiple Conditions
There isn’t much variation in the code when working with multiple conditions. Instead of defining a single condition, you extend the formula to include another dependent condition. Use the formulas listed below to calculate the maximum and minimum Sales value for Furniture in California.
Note the usage of two conditions within the formula. In layman 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.
Using Logical Operators Within Numbers
Logical operators are used in conjuction with MAXIFS and MINIFS functions to specify additional conditions that you can use to enhance the effectiveness of your calculations. The logical operators present in Excel are:
Now, if you want to use any of these operators to return a value based on a condition, you can use it as follows:
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
At times, when you need to search for values that fall within a date range, you can use the MAXIFS and MINIFS functions with ease. To find a value that falls after a specific date, you can use the following:
As you might have guessed, the first formula returns the value 731.94, which is the maximum value between the order dates 02-01-2016 and 15-04-2017. Subsequently, the second formula returns the value 14.62, since the date conditions are the same as before.
If you want to expand this formula and include a range of dates, you can do so in the following manner:
The former function returns the value 957.5775, while 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 look forward to when using the MAXIFS and MINIFS functions with text values. The result usually consists of a value for a specific condition specified within the formula range.
Suppose you want to calculate the maximum and minimum Sales values for California. To do so, you can specify the city name within the formula directly.
When working with text criteria, make sure you match the case of the search value with the original text value. As expected, the maximum value for California is 1706.184, and the minimum value is 7.28.
Working With Conditional Functions in Excel
Excel offers a lot of conditional formulas, making it a cinch to work with different logical statements. Conditional statements like AVERAGEIF, SUMIF, SUMIFS, and other related functionalities make Microsoft Excel a delight for data analysts.
Even though the concept of these statements is more or less the same, a few differences make them stand out from the list of available functions.
