The MAXIFS and MINIFS Functions
Examine the dataset below.
Our objective is to find the highest and lowest Revenue that is in the “Current” year and is part of the “Game” Division.
MAXIFS Syntax
The syntax for the MAXIFS function is as follows:
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- max_range – the range of cells by which you wish to locate the max value.
- criteria_range1 – the range of cells to be evaluated for a criterion.
- criteria1 – the criterion to be discovered in the defined range. This can take the form of a number, an expression, or text.
- criteria_range2, criteria2, … – these are additional criteria that can be defined to refine the search. You can define up to 126 additional range/criteria pairs.
Our values to return from are in column D. We wish to find the Year listed in cell G4 within the column A range, and the Division listed in cell H4 within the column B range.
In our dataset, begin by selecting cell J4 and enter the following formula:
=MAXIFS(D4:D33, A4:A33, G4, B4:B33, H4)
We can see that the formula has returned the largest value from the set where the Year is “Current” and the Division is “Game”.
If you have a series of Years and Divisions listed and wish to copy the formula down to adjacent rows, be sure to lock the range references as defined in the formula below.
=MAXIFS($D$4:$D$33, $A$4:$A$33, G4, $B$4:$B$33, H4)MINIFS Syntax
The syntax for the MINIFS function is as follows:
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- min_range – the range of cells by which you wish to locate the min value.
- criteria_range1 – the range of cells to be evaluated for a criterion.
- criteria1 – the criterion to be discovered in the defined range. This can take the form of a number, an expression, or text.
- criteria_range2, criteria2, … – these are additional criteria that can be defined to refine the search. You can define up to 126 additional range/criteria pairs.
Just as before, our values to return from are in column D. We wish to find the Year listed in cell G9 within the column A range, and the Division listed in cell H9 within the column B range.
In our dataset, begin by selecting cell J9 and enter the following formula:
=MINIFS(D4:D33, A4:A33, G9, B4:B33, H9)
This time the formula has returned the smallest value from the set where the Year is “Current” and the Division is “Game”.
If you need to copy the formula down to adjacent rows, be sure to lock the range references as we did with the MAXIFS formula.
=MINIFS($D$4:$D$33, $A$4:$A$33, G9, $B$4:$B$33, H9)Solution for Users with Excel 2010 and Later
– AGGREGATE Function –
The AGGREGATE function was introduced in Excel 2010 and can be used to accomplish the same objective as the MAXIFS and MINIFS functions.
One of the benefits of using the AGGREGATE function is that it does not require the use of the CTRL-Shift-Enter key combination when finishing the formula. The AGGREGATE function can handle arrays natively without any fancy key combinations.
Another benefit of the AGGREGATE function is its built-in ability to ignore errors in the array results. This will become especially useful in an upcoming example.
For a complete breakdown of the AGGREGATE function and all its argument options, see the following Microsoft Help page.
AGGREGATE to Achieve MAXIFS Behavior
- We will utilize the LARGE option (number 14) and the “Ignore nothing” option (number 4) in the first 2 argument positions.
- Because we are performing logical tests, we must contain each test within parenthesis.
- The final argument will be the “k” position used by the LARGE option. In this case, we will use the number 1 since we want the 1st largest value in the set.
Let’s begin by selecting cell J5 and enter the following formula:
=AGGREGATE(14, 4, (A4:A33=G4) * D4:D33, 1)
The logic of the comparison (A4:A33=G4) produces a series of “True/False” responses because each item in the A4:A33 array is compared to the value in cell G4.
When we multiply the “True/False” responses against the values in range D4:D33, the “True/False” responses are interpreted as 1/0 (ones and zeroes).
Each 1/0 is multiplied against its Revenue pair.
This results in a list of Revenue values where a “True” is encountered, and a 0 (zero) where a “False” is encountered.
AGGREGATE will then isolate and return the 1st largest value in the results list.
Our answer is correct but only form the standpoint of the largest Revenue in the “Current” year. We need to refine the function to also isolate Divisions marked as “Game”.
Let’s update the formula to include searching for “Game” (cell H4) in the Division range (B4:B33).
=AGGREGATE(14, 4, (A4:A33=G4) * (B4:B33=H4) * D4:D33, 1)
Each evaluation set will produce a list of “True/False” responses.
When we multiply these two evaluation sets against one another, we are given a list of ones and zeroes.
Any set of 1×1 will yield a 1. All other combinations (1×0, 0×1, 0×0) yield a 0 (zero).
These will be multiplied against the values in range D4:D33 to isolate those values that are both “Current” year AND “Game” Division.
AGGREGATE to Achieve MINIFS Behavior
Let’s see if we can save some time by recycling the previous AGGREGATE using LARGE function to create an AGGREGATE using SMALL function.
If we copy the formula from cell J5 and paste into cell J10 and modify the first argument from 14 (LARGE) to 15 (SMALL)…
=AGGREGATE(15, 4, (A4:A33=G9) * (B4:B33=H9) * D4:D33, 1)…we see that the formula fails to produce the desired result.
This is because the logic produces a series of values and zeros, and zero will always be the lowest value (assuming there are no negative values in the dataset.)
We need to find a way to cancel the zeroes out of the intermediate results.
If we force the zeroes to become errors, we can utilize an argument option that tells AGGREGATE to ignore the error responses.
If we enclose the two comparison operation within a set of parentheses and divide 1 by those responses (1 is the numerator and the responses are the denominator), we will effectively divide 1 by 1 or 1 by 0.
=AGGREGATE(15, 4, 1 / ((A4:A33=G9) * (B4:B33=H9)) * D4:D33, 1)
All the 1s will remain 1 and the 0s will become errors.
Finally, we’ll update the error handling argument of AGGREGATE to ignore error values. We’ll change the 2nd argument from a 4 to a 3.
=AGGREGATE(15, 3, 1 / ((A4:A33=G9) * (B4:B33=H9)) * D4:D33, 1)Let’s Be Consistent
Even though it’s not necessary to perform the division aspect of the formula when determining the highest Revenue for “Current” “Game”, let’s modify the earlier AGGREGATE formula that utilized the LARGE function just so we have a sense of consistency within our spreadsheet.
=AGGREGATE(14, 3, 1 / ((A4:A33=G4) * (B4:B33=H4)) * D4:D33, 1)
Bonus Method – MAX/MIN Using IF
An alternative to using AGGREGATE would be to use an IF function nested within a MAX or MIN function.
NOTE: This method requires the use of the CTRL-Shift-Enter key sequence when committing the formula.
To find the largest value based on multiple criteria, the formula is as follows:
{=MAX(IF(A4:A33=G4, IF(B4:B33=H4, D4:D33)))}
To find the smallest value based on multiple criteria, the formula is as follows:
{=MIN(IF(A4:A33=G9, IF(B4:B33=H9, D4:D33)))}
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
