MAXIFS returns the largest numeric value whose corresponding cells meet every supplied criterion. Put the values range first, then add each criteria range and its condition as a pair.
MAXIFS function syntax
=MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])- range: the cells containing numbers to maximize.
- criteria_range1 and criterion1: the first test range and condition.
- Additional pairs apply AND logic: all conditions must hold for the same record.
- All ranges must have matching dimensions and align to the same records.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Region | Units | Revenue |
|---|---|---|
| East | 65 | 780 |
| West | 55 | 660 |
| East | 80 | 960 |
| East | 40 | 500 |
Find the maximum for one region
Enter this formula in A8. The East revenues are 780, 960 and 500. MAXIFS returns 960. It returns the amount, not the name or full row belonging to that amount.
=MAXIFS(C2:C5,A2:A5,"East")Result: 960.
Apply a numeric threshold
This keeps rows with more than 50 units. The qualifying revenues are 780, 660 and 960, so the maximum remains 960. A row with exactly 50 units would not qualify.
=MAXIFS(C2:C5,B2:B5,">50")Result: 960.
Require two text matches
Enter the following supporting tables at A20, A30 and A40 respectively. This formula uses the student table: both Aanya and Math must match, so the Science score of 98 is excluded.
| Student | Subject | Score |
|---|---|---|
| Aanya | Math | 92 |
| Aanya | Math | 95 |
| Aanya | Science | 98 |
| Date | Amount |
|---|---|
| =DATE(2026,5,1) | 820 |
| =DATE(2026,5,31)+0.5 | 1150 |
| =DATE(2026,6,1) | 2000 |
| Department | Years | Bonus |
|---|---|---|
| Sales | 7 | 2400 |
| Sales | 8 | 3100 |
| Sales | 3 | 4000 |
| Engineering | 6 | 5000 |
=MAXIFS(C21:C23,A21:A23,"Aanya",B21:B23,"Math")Result: 95.
Include a whole month, even with timestamps
Use the date table at A30. The lower bound includes May 1 and the upper bound excludes June 1. The May 31 noon transaction remains included, giving a maximum of 1150.
=MAXIFS(B31:B33,A31:A33,">="&DATE(2026,5,1),A31:A33,"<"&DATE(2026,6,1))Result: 1150.
Combine a department with a tenure threshold
The bonus table starts at A40. Sales employees with at least five years have bonuses of 2400 and 3100. Other departments and shorter tenure do not qualify.
=MAXIFS(C41:C44,A41:A44,"Sales",B41:B44,">=5")Result: 3100.
Distinguish no matches from a real zero
The region North has no rows. A COUNTIF check returns an explanatory message instead of displaying MAXIFS’s no-match result of zero. For multiple conditions, use COUNTIFS with the same pairs.
=IF(COUNTIF(A2:A5,"North")=0,"No matching rows",MAXIFS(C2:C5,A2:A5,"North"))Result: No matching rows.
Other Google Sheets articles you may also like