How to Use MAXIFS and MINIFS Functions in Excel
Microsoft Office continues to enchant its users with its fancy, yet valuable formulas. There are probably many useful functions in Microsoft Excel that aren’t used on a daily basis, despite their utility.
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 canlearn 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’re able to hope to use them.
Practice Dataset
You can use a sample dataset to understand the concepts of MAXIFS and MINIFS functions.
Order Date
Sub-Category
08-11-2016
California
12-06-2016
Office Supplies
11-10-2015
09-06-2014
Furnishings
Technology
Appliances
North Carolina
15-04-2017
Washington
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:
You can optionally add more ranges and criteria arguments to the function as follows:
Since you are dealing with multiple layers of formulas and their individual arguments, you should always try tounderstand 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’re able to 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, sayTables, use the formula shown below:
The above formula takes the following arguments:
On similar grounds, you can calculate the minimum profit for Tables by defining the range and criteria as follows:
The maximum value is85.3092and 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 is1706.184while the minimum value is48.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:
Greater than
Greater than equal to
Less than equal to
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 of48.86from the Sales column. The second formula returns the minimum value, which is7.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 may 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 value731.94, which is the maximum value between the order dates02-01-2016and15-04-2017. Subsequently, the second formula returns the value14.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 value957.5775, while the latter returns the corresponding value of14.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 minimumSalesvalues forCalifornia. To do so, you may 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 is1706.184, and the minimum value is7.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.
You can use the AVERAGEIF function to calculate the average only for cells that meet certain criteria. Here’s how to use it.
You don’t need to fork out for expensive hardware to run an AI on your PC.
Now, I actually finish the books I start.
My foolproof plan is to use Windows 10 until 2030, with the latest security updates.
Taming data is easier than it looks.
Not all true crime is about hacking, slashing, and gore.
