Home › Functions › Statistical › =MAXIFS()
Statistical Excel 2019 ◉ 22 views

=MAXIFS()

The MAXIFS function returns the maximum value among cells specified by a given set of conditions or criteria: numbers, dates, text, etc. This feature is 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 sure you have the latest version of Office.

Applies To: Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Hover argument names in the table for details.
Output typeNumber
Popularity30 / 100
VolatileNo
Dynamic ArrayNo
📥 Download sample .xlsx ▦ Try in Playground Dedicated file for this function — safe & lightweight · sample data + live formulas
Share:

⬇ Download Sample Workbook

A dedicated .xlsx for =MAXIFS() with Persian sample data & live formulas — open in Excel and practice.

  • Sheet “Sample” — data table + formula in place
  • Sheet “Guide” — syntax, arguments & tips from this page
  • Instant & secure (token + rate limit)
📥 Download MAXIFS.xlsx ~ 8–12 KB · No macros

⇪ Share

Send this function to friends

🧠 🧠 Quiz this function

%d questions — syntax, example & errors — test yourself!

◧ Arguments

ArgumentTypeRequiredDescription
max_range Range Required The actual range of cells in which the maximum will be determined.
criteria_range1 Range Required Is the set of cells to evaluate with the criteria.
criteria1 Any type Required Is the criteria in the form of a number, expression, or text that defines which cells will be evaluated as maximum. The same set of criteria works for the MINIFS, SUMIFS, and AVERAGEIFS functions.
criteria_range2, criteria2 Number Optional Additional ranges and their associated criteria. You can enter up to 126 range/criteria pairs.

▣ Examples

▦ Data
CellValue
A2 Grade
B2 Weight
A3 89
B3 1
A4 93
B4 2
A5 96
B5 2
A6 85
B6 3
A7 91
B7 1
A8 88
B8 1
A9 Weight
B9 Grade
A10 10
B10 b
A11 1
B11 a
A12 100
B12 a
A13 1
B13 b
A14 1
B14 a
A15 1
B15 a
A16 Weight
B16 Grade
C16 Class
D16 Level
A17 10
B17 b
C17 Business
D17 100
A18 1
B18 a
C18 Technical
D18 100
A19 100
B19 a
C19 Business
D19 200
A20 1
B20 b
C20 Technical
D20 300
A21 1
B21 a
C21 Technical
D21 100
A22 50
B22 b
C22 Business
D22 400
A23 Weight
B23 Grade
C23 Class
D23 Level
A24 10
B24 b
C24 Business
D24 8
A25 1
B25 a
C25 Technical
D25 8
A26 100
B26 a
C26 Business
D26 8
A27 11
B27 b
C27 Technical
D27 0
A28 1
B28 a
C28 Technical
D28 8
A29 12
B29 b
C29 Business
D29 0
A30 Weight
B30 Grade
A31 10
B31 b
A32 1
B32 a
A33 100
B33 a
A34 1
B34 b
A35 1
B35 a
A36 1
B36 a
A37 Weight
B37 Grade
C37 Class
D37 Level
A38 10
B38 b
C38 Business
D38 100
A39 1
B39 a
C39 Technical
D39 100
A40 100
B40 a
C40 Business
D40 200
A41 1
B41 b
C41 Technical
D41 300
A42 1
B42 a
C42 Technical
D42 100
A43 1
B43 a
C43 Business
D43 400
A44 Grade
B44 Weight
A45 89
B45 1
A46 93
B46 2
A47 96
B47 2
A48 85
B48 3
A49 91
B49 1
A50 88
B50 1
A51 Weight
B51 Grade
A52 10
B52 b
A53 1
B53 a
A54 100
B54 a
A55 1
B55 b
A56 1
B56 a
A57 1
B57 a
A58 Weight
B58 Grade
C58 Class
D58 Level
A59 10
B59 b
C59 Business
D59 100
A60 1
B60 a
C60 Technical
D60 100
A61 100
B61 a
C61 Business
D61 200
A62 1
B62 b
C62 Technical
D62 300
A63 1
B63 a
C63 Technical
D63 100
A64 50
B64 b
C64 Business
D64 400
A65 Weight
B65 Grade
C65 Class
D65 Level
A66 10
B66 b
C66 Business
D66 8
A67 1
B67 a
C67 Technical
D67 8
A68 100
B68 a
C68 Business
D68 8
A69 11
B69 b
C69 Technical
D69 0
A70 1
B70 a
C70 Technical
D70 8
A71 12
B71 b
C71 Business
D71 0
A72 Weight
B72 Grade
A73 10
B73 b
A74 1
B74 a
A75 100
B75 a
A76 1
B76 b
A77 1
B77 a
A78 1
B78 a
A79 Weight
B79 Grade
C79 Class
D79 Level
A80 10
B80 b
C80 Business
D80 100
A81 1
B81 a
C81 Technical
D81 100
A82 100
B82 a
C82 Business
D82 200
A83 1
B83 b
C83 Technical
D83 300
A84 1
B84 a
C84 Technical
D84 100
A85 1
B85 a
C85 Business
D85 400
91In criteria_range1 the cells B2, B6, and B7 match the criteria of 1. Of the corresponding cells in max_range, A6 has the maximum value. The result is therefore 91. =MAXIFS(A2:A7,B2:B7,1)
10Note: The criteria_range and max_range aren't aligned, but they are the same shape and size.In criteria_range1, the 1st, 2nd, and 4th cells match the criteria of "a." Of the corresponding cells in max_range, A2 has the maximum value. The result is therefore 10. =MAXIFS(A2:A5,B3:B6,"a")
50In criteria_range1, B2, B5, and B7 match the criteria of "b." Of the corresponding cells in criteria_range2, D5 and D7 match the criteria of >100. Finally, of the corresponding cells in max_range, A7 has the maximum value. The result is therefore 50. =MAXIFS(A2:A7,B2:B7,"b",D2:D7,">100")
12The criteria2 argument is A8. However, because A8 is empty, it is treated as 0 (zero). The cells in criteria_range2 that match 0 are D5 and D7. Finally, of the corresponding cells in max_range, A7 has the maximum value. The result is therefore 12. =MAXIFS(A2:A7,B2:B7,"b",D2:D7,A8)
#VALUE!Because the size and shape of the max_range and criteria_range aren't the same, MAXIFS returns the #VALUE! error. =MAXIFS(A2:A5,B2:c6,"a")
0No cells match the criteria. =MAXIFS(A2:A6,B2:B6,"a",D2:D6,">200")

§ Remarks

  • The size and shape of the max_range and criteria_rangeN arguments must be the same, otherwise these functions return the #VALUE! error.

⚠ Common Errors

#VALUE! The size and shape of the max_range and criteria_rangeN arguments must be the same, otherwise these functions return the #VALUE! error.
Fix: Make the value range and the criteria ranges the same size

⇄ Related Functions

=LARGE() Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score. =MAX() Returns the largest value in a set of values. =MINIFS() The MINIFS function returns the minimum value among cells specified by a given set of conditions or criteria. =DMAX() Returns the largest number in a field (column) of records in a list or database that matches conditions you that specify.

💬 Comments & Q&A 0 approved

🔒 Your comment will be published after review.

Share your thoughts — visible after admin approval

No comments yet — be the first!

✍️ Add comment / question

captcha
🔒 Your comment will be published after review.