How to find maximum and minimum values in Excel, with criteria
Use MAX and MIN for the largest and smallest numbers in the whole range. Use MAXIFS and MINIFS when the values must meet criteria. With two criteria, each included value must match both at the same relative position.
Find the maximum and minimum of the full range
=MAX(C2:C7)
=MIN(C2:C7)
For the numbers in C2:C7 below, the first formula gives 95 and the second gives 0. In a reference, blank cells and text are ignored, while a numeric zero is included. An all-empty numeric range can also produce 0, so verify the source values.
The AutoSum menu on the Home tab can also insert these functions. Check the first and last cells of any automatically selected range before confirming.
Match the result range to the criteria ranges
The first argument is the range of scores to evaluate. Add each condition as a pair of a range and a criterion. Use the same shape and corresponding rows, as with C2:C7, A2:A7, and B2:B7 below. An offset of one row can associate a condition with the wrong score even if the sizes match.
The conditional functions are available in Microsoft 365 and supported perpetual versions, including Excel 2019. If Excel does not recognize one, check the installed version and formula language.
Calculate scores for a group and a category
Enter the table in A1:C7. Group A has a maximum of 95 and a minimum of 54. Add the category X criterion and the matching scores narrow to 72 and 60.
| Group | Category | Score |
|---|---|---|
| A | X | 72 |
| A | X | 60 |
| A | Y | 95 |
| B | X | 83 |
| B | Y | 0 |
| A | Y | 54 |
The formulas below calculate the extremes for all of group A first, then add the X category. Put a literal text criterion in quotes. If a criterion is in another cell, refer to that cell without quotes. For a threshold such as at least 60, enter ">=60" as the criterion.
=MAXIFS(C2:C7,A2:A7,"A")
=MINIFS(C2:C7,A2:A7,"A")
=MAXIFS(C2:C7,A2:A7,"A",B2:B7,"X")
=MINIFS(C2:C7,A2:A7,"A",B2:B7,"X")
Check whether a zero result has any matching rows
A conditional minimum of zero can represent a real zero or no matching rows. In the example, B and Y match one row with a score of 0. Z and Y match no rows and can still yield 0.
=COUNTIFS(A2:A7,"B",B2:B7,"Y")
=COUNTIFS(A2:A7,"Z",B2:B7,"Y")
Use COUNTIFS with the same criteria to tell them apart. The formulas below return 1 for B and Y and 0 for Z and Y. A count of zero identifies an empty result set.




