Excel's MAXIFS and MINIFS functions are handy when finding the maximum or minimum value from a range of data based on specific conditions. These functions allow you to apply one or more criteria to determine the result, making them more versatile than the simpler MAX and MIN functions.
Syntax
| Function | Description |
|---|---|
| MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Returns the maximum value in a range of cells, considering one or more criteria. |
| MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Returns the minimum value in a range of cells, considering one or more criteria. |
Parameters
- max_range / min_range: The range of numbers from which you want to return the maximum or minimum value.
- criteria_range1, criteria_range2, ...: The ranges where the conditions are checked.
- criteria1, criteria2, ...: The criteria to match in the corresponding ranges.
Example
Let’s look at a slightly complicated example using both MAXIFS and MINIFS functions.
| Employee | Department | Sales | Month |
|---|---|---|---|
| John | Sales | 12000 | Jan |
| Sara | Marketing | 15000 | Feb |
| Mike | Sales | 13000 | Jan |
| Anna | Marketing | 17000 | Mar |
| Sam | Sales | 11000 | Feb |
Task: Using this table, find:
- The maximum sales from the Sales department in January.
- The minimum sales in February, regardless of the department.
To solve these:
- Use MAXIFS to find the highest sales in January from the Sales department.
- Use MINIFS to get the lowest sales in February.
Solution for Example
MAXIFS to find the maximum sales in January from the Sales department:
=MAXIFS(C2:C6, B2:B6, "Sales", D2:D6, "Jan")This formula checks for "Sales" in the Department column (B2:B6) and "Jan" in the Month column (D2:D6), then returns the maximum value from the Sales column (C2:C6), which is 13000.
MINIFS to find the minimum sales in February, regardless of department:
=MINIFS(C2:C6, D2:D6, "Feb")Practice Exercise 1
Task: Using the table below, find the maximum salary for employees in the IT department who have more than 5 years of experience.
Hint: You can use the MAXIFS function with two criteria:
- The department is IT.
- The years of experience are greater than 5.
Practice Exercise 2
Task: Using the table below, find the minimum order value for customers from the USA who ordered more than 10 units.
Hint: Use the MINIFS function with two criteria:
- The country is the USA.
- The units ordered are greater than 10.
This formula checks for "Feb" in the Month column (D2:D6) and returns the minimum value from the Sales column (C2:C6), which is 11000.
Solution for Practice Exercise 1
To find the maximum salary for IT employees with more than 5 years of experience:
=MAXIFS(D2:D6, B2:B6, "IT", C2:C6, ">5")This checks for "IT" in the Department column (B2:B6) and greater than 5 in the Years of Experience column (C2:C6), and returns the maximum salary, which is 78000.
Solution for Practice Exercise 2
To find the minimum order value for USA customers who ordered more than 10 units:
=MINIFS(C2:C6, B2:B6, "USA", D2:D6, ">10")This checks for "USA" in the Country column (B2:B6) and greater than 10 in the Units Ordered column (D2:D6), returning the minimum order value, which is 1100.
Now, the parentheses are properly enclosed around the ranges.