The MAXIFS function returns the optimum worth amongst cells defined by an offered set of conditions or criteria.
Syntax
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
|
Argument |
Description |
|---|---|
|
max_range |
The real series of cells in which the maximum will be determined. |
|
criteria_range1 |
Is the set of cells to examine with the criteria. |
|
criteria1 |
Is the criteria in the form of a number, expression, or text that specifies which cells will be assessed as optimum. The same set of criteria works for the MINIFS, SUMIFS, and AVERAGEIFS functions. |
|
criteria_range2, |
Extra varieties and their associated requirements. You can go into as much as 126 range/criteria pairs. |
Remarks
Examples
Copy the example information in each of the following tables, and paste it in cell A1 of a new Excel worksheet. For solutions to show results, choose them, press F2, and then press Enter. If you need to, you can change the column widths to see all the data.
Example 1
|
Grade |
Weight |
|---|---|
|
89 |
1 |
|
93 |
2 |
|
96 |
2 |
|
85 |
3 |
|
91 |
1 |
|
88 |
1 |
|
Formula |
Result |
|
=MAXIFS(A2: A7, B2: B7,1) |
91 In criteria_range1 the cells B2, B6, and B7 match the requirements of 1. Of the matching cells in max_range, A6 has the maximum worth. The outcome is therefore 91. |
Example 2
|
Weight |
Grade |
|---|---|
|
10 |
b |
|
1 |
a |
|
100 |
a |
|
1 |
b |
|
1 |
a |
|
1 |
a |
|
Formula |
Result |
|
=MAXIFS(A2: A5, B3: B6,”a”) |
10 Keep in mind: The criteria_range and max_range aren’t aligned, however they are the very same shape and size. In criteria_range1, the 1st, second, and 4th cells match the requirements of “a.” Of the corresponding cells in max_range, A2 has the optimum value. The outcome is therefore 10. |
Example 3
|
Weight |
Grade |
Class |
Level |
|---|---|---|---|
|
10 |
b |
Company |
100 |
|
1 |
a |
Technical |
100 |
|
100 |
a |
Organization |
200 |
|
1 |
b |
Technical |
300 |
|
1 |
a |
Technical |
100 |
|
50 |
b |
Company |
400 |
|
Formula |
Result |
||
|
=MAXIFS(A2: A7, B2: B7,”b”, D2: D7,”> 100″) |
50 In criteria_range1, B2, B5, and B7 match the criteria of “b.” Of the matching cells in criteria_range2, D5 and D7 match the criteria of > 100. Finally, of the corresponding cells in max_range, A7 has the optimum value. The outcome is therefore 50. |
Example 4
|
Weight |
Grade |
Class |
Level |
|---|---|---|---|
|
10 |
b |
Organization |
8 |
|
1 |
a |
Technical |
8 |
|
100 |
a |
Company |
8 |
|
11 |
b |
Technical |
0 |
|
1 |
a |
Technical |
8 |
|
12 |
b |
Organization |
0 |
|
Formula |
Result |
||
|
=MAXIFS(A2: A7, B2: B7,”b”, D2: D7, A8) |
12 The criteria2 argument is A8. However, due to the fact that A8 is empty, it is dealt with as 0 (absolutely no). The cells in criteria_range2 that match 0 are D5 and D7. Lastly, of the matching cells in max_range, A7 has the maximum worth. The result is therefore 12. |
Example 5
|
Weight |
Grade |
|---|---|
|
10 |
b |
|
1 |
a |
|
100 |
a |
|
1 |
b |
|
1 |
a |
|
1 |
a |
|
Formula |
Result |
|
=MAXIFS(A2: A5, B2: c6,”a”) |
#VALUE! Since the size and shape of the max_range and criteria_range aren’t the very same, MAXIFS returns the #VALUE! error. |
Example 6
|
Weight |
Grade |
Class |
Level |
|---|---|---|---|
|
10 |
b |
Service |
100 |
|
1 |
a |
Technical |
100 |
|
100 |
a |
Service |
200 |
|
1 |
b |
Technical |
300 |
|
1 |
a |
Technical |
100 |
|
1 |
a |
Organization |
400 |
|
Formula |
Result |
||
|
=MAXIFS(A2: A6, B2: B6,”a”, D2: D6,”> 200″) |
0 No cells match the requirements. |
Need more assistance?
You can constantly ask a professional in the Excel Tech Community or get assistance in Communities.
See Also
MINIFS function
SUMIFS function
AVERAGEIFS function
COUNTIFS function
The MAXIFS function returns the optimum worth amongst cells defined by a provided set of conditions or criteria: numbers, dates, text, and so on. This function is readily available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make certain you have the current version of Workplace.
