MAXIFS is a function in Excel that allows you to find the maximum value in a range that meets specific criteria. It’s an extremely useful function when dealing with large sets of data, as it allows you to quickly and easily identify the highest value that meets certain conditions. This is similar to MINIFS which allows you to identify the lowest value which meets certain conditions. In this tutorial, we’ll go over the different ways you can use MAXIFS in Excel, including examples with multiple criteria, wildcards, not equals criteria, and not blank cells.
Function Fact Sheet: MAXIFS
Before we dive into the different examples, let’s first review the basic syntax for the MAXIFS function:
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
max_range: This is the range of cells that you want to find the maximum value in.criteria_range1: This is the range of cells that you want to apply the first criteria to.criteria1: This is the first criteria that you want to apply to the first criteria range.[criteria_range2, criteria2]: These are optional additional criteria ranges and criteria that you want to apply to them.
Now that we understand the basic syntax for MAXIFS, let’s move on to the different examples for usage.
Using MAXIFS with multiple criteria
One of the most powerful features of the MAXIFS function is the ability to use multiple criteria. This allows you to find the maximum value that meets more than one condition. For example, let’s say you have a table of sales data and you want to find the highest sales amount for a specific product in a specific month. Here’s how you would do it:
First, select the cell where you want to display the maximum value. Then, type “=” to start the formula. Next, type “MAXIFS(” to start the MAXIFS function.
Now, select the range of cells that contains the sales amounts (max_range), and type a comma to move to the next argument.
Next, select the range of cells that contains the months (criteria_range1), and type a comma to move to the next argument.
Type the month you want to search for (criteria1), surrounded by quotes. For example, if you want to find the highest sales amount for January, you would type “January”.
Type another comma to move to the next argument. Now, select the range of cells that contains the products (criteria_range2), and type another comma to move to the next argument.
Finally, type the name of the product you want to search for (criteria2), surrounded by quotes. For example, if you want to find the highest sales amount for the product “Widget A” in January, you would type “Widget A”.
The complete formula would look like this:
=MAXIFS(B2:B10, A2:A10, "January", C2:C10, "Widget A")
Press enter to calculate the result, and the cell will display the highest sales amount for the product “Widget A” in January.
Using MAXIFS with wildcards
Another powerful feature of the MAXIFS function is the ability to use wildcards. Wildcards are symbols that represent any character or group of characters, and they can be used to match a pattern in a text string. This allows you to search for values that match a certain pattern, even if you don’t know the exact value you’re looking for.
For example, let’s say you have a table of employee data and you want to find the highest salary for all employees whose job title contains the word “Manager”. Here’s how you would do it:
First, select the cell where you want to display the maximum value. Then, type “=” to start the formula. Next, type “MAXIFS(” to start the MAXIFS function.
Now, select the range of cells that contains the salaries (max_range), and type a comma to move to the next argument.
Next, select the range of cells that contains the job titles (criteria_range1), and type a comma to move to the next argument.
Type the criteria you want to search for (criteria1), using a wildcard asterisk (*) to represent any characters before or after the word “Manager”, i.e. “*Manager*”. For example, this would match job titles such as “Project-Manager” and “Manager of People”.
The complete formula would look like this:
=MAXIFS(B2:B10, A2:A10, "*Manager*")
Press enter to calculate the result, and the cell will display the highest salary for all employees whose job title contains the word “Manager”.
Using MAXIFS with not equals criteria
In some cases, you may want to find the maximum value that does not meet a certain criteria. For example, let’s say you have a table of test scores and you want to find the highest score that is not an A. Here’s how you would do it:
First, select the cell where you want to display the maximum value. Then, type “=” to start the formula. Next, type “MAXIFS(” to start the MAXIFS function.
Now, select the range of cells that contains the test scores (max_range), and type a comma to move to the next argument.
Next, select the range of cells that contains the grade letters (criteria_range1), and type a comma to move to the next argument.
Type the criteria you want to exclude (criteria1), using the not equals operator (<>). For example, if you want to exclude all A grades, you would type “<>A”.
The complete formula would look like this:
=MAXIFS(B2:B10, A2:A10, "<>A")
Press enter to calculate the result, and the cell will display the highest score that is not an A.
Using MAXIFS with not blank criteria
Finally, you can also use the MAXIFS function to find the highest value in a range that is not blank. This can be useful when dealing with data that has missing values. For example, let’s say you have a table of product prices and you want to find the highest price that is not blank. Here’s how you would do it:
First, select the cell where you want to display the maximum value. Then, type “=” to start the formula. Next, type “MAXIFS(” to start the MAXIFS function.
Now, select the range of cells that contains the product prices (max_range), and type a comma to move to the next argument.
Next, select the same range of cells as the criteria range (criteria_range1), and type a comma to move to the next argument.
Type the criteria you want to apply (criteria1), using the not equal to operator (<>). For example, if you want to exclude all blank cells, you would type “<>”.
The complete formula would look like this:
=MAXIFS(B2:B10, B2:B10, "<>")
Press enter to calculate the result, and the cell will display the highest price that is not blank.
Conditional MAXin Older Versions of Excel
It’s great if you have Excel 2019 or later and can use the MAXIFS function. However, in some cases you won’t have this version. Particularly in a corporate environment where there is often a lag updating to the latest software versions. As I write this in 2023. I have one work environment where I need to use Excel 2010! This means that spreadsheets developed in the more recent version have to be backwards compatible so can’t take advantage of new formulas.
If that’s the case, then you can achieve a similar result to MAXIFS using MAX with array formula. Using the example data above, we can rewrite this as an array formula as follows. Remember when entering an array formula to press SHIFT + ENTER after typing the formula in, otherwise it may not behave as expected.
To find the maximum insurance account number (i.e. maximum of C2:C7 where B2:B7 equals “Insurance”):
- MAXIFS version:
=MAXIFS($C$2:$C$7,$B$2:$B$7,"Insurance") - Array formula:
=MAX(IF($B$2:$B$7="Insurance",$C$2:$C$7,""))
To find the maximum positive account balance (i.e. maximum of C2:C7 where those values are greater than zero):
- MAXIFS version:
=MAXIFS($C$2:$C$7,$C$2:$C$7,">0") - Array formula:
=MAX(IF($C$2:$C$7>0,$C$2:$C$7,""))
In these cases, we are replacing the values which do not meet our criteria with an empty string value (“”), which is then ignored by MAX(), so that only those values which meet our criteria are included.
It’s also possible to specify multiple criteria, but unfortunately the AND function doesn’t work as you might think in array formulas (it only returns a single value rather than an array), so we have to build this up as a series of nested IF functions.
With our example, to find the maximum positive account balance of insurance contracts, we could do:
- MAXIFS version:
=MAXIFS($C$2:$C$7,$C$2:$C$7,">0",$B$2:$B$7,"Insurance") - Array formula:
=MAX(IF($C$2:$C$7>0,IF($B$2:$B$7="Insurance",$C$2:$C$7,""),""))
Conclusion
In conclusion, the MAXIFS function is a powerful tool in Excel that allows you to find the maximum value in a range that meets specific criteria. By using multiple criteria, wildcards, not equals criteria, and not blank cells, you can easily analyze and make sense of large sets of data. With the examples provided in this tutorial, you should be able to apply the MAXIFS function to your own data and start getting insights right away. We’ve also shown a method to achieve the same results as MAXIFS on earlier versions of Excel by using array formulas.