Use the Excel MAXIFS function to find the maximum value based on supplied criteria.
The MAXIFS worksheet function is available in all Microsoft Excel versions since Excel 2019. Prior to its release, an array formula based on using IF and MAX was required. An example of this array formula is shown at the end of this article.
In this article, we will walk you through the essentials of the Excel MAXIFS function, its syntax, and practical examples. By the end, you’ll be equipped with the knowledge to use this valuable function and elevate your data analysis skills.
Download the sample file to practise.
Short Summary of Article
Excel MAXIFS Function returns maximum value from a range based on criteria
Practical Excel MAXIFS function examples and advanced techniques
Learn common errors associated with the function & their solutions
Using MAXIFS Function: Step-by-Step Guide
The MAXIFS function, a built-in Statistical function in Excel, is designed to return the maximum value in a range that meets one or more criteria.
Its applications are versatile, covering various scenarios, such as finding the maximum sales figure for any quarter, area, or sales rep (or any combination of these). In essence, the MAXIFS function allows you to obtain the maximum values based on multiple conditions in a convenient and efficient manner.
Syntax and Arguments
The syntax for the MAXIFS function in Microsoft Excel is as follows:
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2,…)The function requires the following arguments:
max_range: this first argument is the range of cells from which you want to find the max value
criteria_range1: the range of cells that you want to apply the first criteria to
criteria1: the criteria that you want to apply to criteria_range1
[criteria_range2, criteria2], …: additional ranges and criteria that you want to apply. These are optional. Only the first three arguments are required.
The function returns the largest numeric value from the corresponding cells based on the given criteria.
Let’s see examples of the MAXIFS function.
Excel MAXIFS with Single Criterion
The MAXIFS function can be used with single criterion or multiple conditions.
For example, the following MAXIFS formula returns the maximum value for the region stated in cell E3.
=MAXIFS(tblSingle[Total],tblSingle[Region],E3)
In this example, the logical test was to find a match for a text string. This could have entered directly into the criteria argument of the MAXIFS function using double quotation marks, e.g., “North”.
Using criteria based on a cell reference to cell E3 means that the region can easily be changed on the sheet by users. A dynamic value.
When you need to get the maximum value based on the result of testing numeric values, logical operators such as >, <, and >= will be required. These can be included in the text string with the numeric value e.g., “>=25”, or if the number is stored in a cell, concatenated to the cell reference e.g., “>=”&A3.
In this example, the formula returns the maximum from the [Sales] column based on the conditional test of the length of service value being less than or equal to 3.
=MAXIFS(tblTeam[Sales],tblTeam[Service (yrs)],"<=3")
Excel MAXIFS Function with Multiple Criteria
You can use the MAXIFS function with up to 126 conditions. That is more than you will ever need, or should use. Let’s see an example of Excel MAXIFS with two conditions.
The following formula is used to find the maximum sales figure for the sales rep stated in cell E3 and the region stated in cell F3. The MAXIFS function achieves this with ease. The largest value of 873 is returned.
=MAXIFS(tblRegions[Total],tblRegions[Region],F3,tblRegions[Rep],E3)
Advanced Techniques with MAXIFS
Advanced techniques with the MAXIFS function include utilising it to determine the most recent date with specific criteria and using MAXIFS with wildcard characters.
The potential of MAXIFS can be further leveraged by combining it with other Excel functions, allowing for more intricate equations and computations. In the following sections, we’ll explore these three advanced Excel, MAXIFS function examples.
Return the Last Date that Matches Criteria
In certain situations, you may need to find the most recent date that meets specific criteria in a dataset. The MAXIFS function can be employed for this purpose, as it can identify the most recent date (maximum value) that meets the specified conditions.
The following formula returns the last date where there is a match for the team specified in cell B1..
=MAXIFS(tblInspections[Date],tblInspections[Team],B1)
This technique allows you to effectively find the last date with specific criteria, making the MAXIFS function even more valuable for data analysis.
MAXIFS Formula with Wildcard Characters
Let’s see how to use the MAXIFS function with wildcard characters to perform partial matches in criteria ranges.
A wildcard character allows partial matching of values by acting as another character, or multiple characters. The question mark is used to replace a single unknown character, and the asterisk is used to replace multiple unknown characters.
For instance, the following Excel MAXIFS function returns the largest value in the [Total] column where the value in the [Region] column begins with the text entered in cell E3.
=MAXIFS(tblSales[Total],tblSales[Region],E3&"*")The asterisk is appended to the text string from cell E3 using the ampersand. This replaces any characters that may follow “NE” in this instance. The max value of 375 is returned.
Combining MAXIFS with Other Functions
The power of the MAXIFS function can be further harnessed by combining it with other Excel functions. For instance, combining MAXIFS with an IF function to operate only if a condition is true, or with a lookup formula to return a corresponding value to the maximum number.
For this example, let’s use the MAXIFS function to return the maximum sales value after a specified date. The DATE function is used to allow the entry of a date value into Excel MAXIFS, and logical operators have been entered as a string and concatenated with DATE to build a logical test.
=MAXIFS(tblDate[Total],tblDate[Date],">="&DATE(2023,5,1))
Common Errors and Solutions
Working with the MAXIFS function can sometimes lead to errors. These errors can be easily addressed once you are familiar with the syntax and specific requirements with how to use the MAXIFS function in Excel.
Max range and criteria range must be the same size
The range of cells used for the max range and criteria range arguments must be the same size. If the corresponding ranges are not equal in size, the #VALUE! formula error is returned.
=MAXIFS(C:C,A2:A12,E3)
The Max range must contain numeric values
MAXIFS returns the maximum value from a column containing numeric values only. In this formula, the max range of cells contain text, so 0 is returned by the MAXIFS formula.
To convert text to number quickly, you can use Text to Columns or use a formula such as NUMBERVALUE.
Numeric value tests are entered as text criteria
A common mistake when getting started with the MAXIFS formula is to forget the double quotation marks when entering logical tests with numbers. This is unexpected, though is consistent with the other functions in the IFS family such as SUMIFS.
The condition can be entered directly into the quotations such as “>=20”, or concatenated with a number stored in a cell or defined name, such as “>=”&B2, or “>=”&Threshold. We have seen two examples of this within this tutorial.
Summary
In conclusion, the MAXIFS function is a valuable tool for anyone working with data analysis in Excel. It allows you to find the max value based on multiple criteria with ease, making your data analysis tasks more efficient and accurate.
By understanding the syntax, arguments, and practical examples, as well as exploring advanced techniques and common errors, you can greatly enhance your Excel skills and tackle even the most complex data analysis challenges with confidence.
Learn more than 150 of the best Excel functions with my Advanced Excel Formulas book. Over 500 formula examples with sample files to practise.
Frequently Asked Questions
Is there a MAXIFS function in Excel?
Yes, the MAXIFS function is available in Excel. It is used to find the maximum value in a range of cells that meet one or more criteria.
How do I use MAXIFS in Excel?
To utilise the MAXIFS function in Excel, simply enter a formula using the function syntax =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2,…]) into the cell that you want to use.
You must fill in the “max_range”, as well as criteria_range1/criteria1 and any additional criteria_range/criteria pairs. Doing so allows you to quickly get the maximum value within the range specified.
Is MAXIFS available in Excel 2016?
No, MAXIFS is not available in Excel 2016. It is only supported in versions since Microsoft Excel 2019.
What is the main purpose of the MAXIFS function in Excel?
The MAXIFS function in Excel allows users to easily identify the highest value among data that meets specific conditions, enabling them to quickly analyze large datasets.
What is the alternate function for MAXIFS in Excel?
An array formula using the IF and MAX function in the style =MAX(IF(criteria,max_range)) can be used if you do not have the MAXIFS function as a built in function in your Microsoft Excel version.
Let’s see an array formula example.
In this formula, the combination of the MAX function and the IF Function are used to replicate the example of MAXIFS with multiple conditions shown earlier in this article.
=MAX(IF(((tblArray[Region]=F3)*(tblArray[Rep]=E3)),tblArray[Total]))
The asterisk (*) is used between the two conditional expressions to create AND logic. If a value other than zero is returned, the logical test results in TRUE and the max range is provided to the MAX function.
You can learn more about how logical expressions in array formulas work in the Excel FILTER function tutorial.
