The Excel MAXIFS function returns the largest numeric value that meets one or more criteria in a range of values. MAXIFS can be used with criteria based on dates, numbers, text, and other conditions. MAXIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
Syntax:= MAXIFS (max_range, range1, criteria1, [range2], [criteria2], …)
The MAXIFS function syntax has the following arguments:
- Max_range (required argument) – The actual range of cells from which the maximum value will be determined.
- Criteria_range (required argument) – The set of cells to be evaluated with the criteria.
- Criteria1 (required argument) – Here, we give a number, expression, or text that defines which cells will be evaluated as maximum.
- Criteria_range2 – An optional argument wherein we can specify additional ranges and their associated criteria.
Example: Let’s look at some Excel MAXIFS function examples and explore how to use the MAXIFS function as a worksheet function in Microsoft Excel:
As an example, let’s find the tallest football player in our local school. Assuming the students’ heights are in cells D2:D17 (max_range) and sports are in B2:B17 (criteria_range1), use the word “football” as criteria1, and you will get this formula:
Example 2: Find max value based on multiple criteria
Supposing, you want to find the tallest basketball player in junior school. To have it done, define the following arguments:
- Max_range – a range of cells containing heights – D2:D17.
- Criteria_range1 – a range of cells containing sports – B2:B17.
- Criteria1 – “basketball”, which is input in cell G1.
- Criteria_range2 – a range of cells defining the school type – C2:C17.
- Criteria2 – “junior”, which is input in cell G2.
Putting the arguments together, we get these formulas:
With “hardcoded” criteria:
As an extra bonus, I will show you a quick way to extract a value from another cell that is associated with the max value. In our case, that will be the name of the tallest person. For this, we will be using the classic INDEX MATCH formula and nest MAXIFS in the first argument of MATCH as the lookup value:
Result:The formula tells us that the name of the tallest basketball player in junior school is Jack Albertson
To see how it works in practice, let’s add the Age column (column C) to our sample table and find the maximum height among the students aged between 13 and 14. This can be done with the following criteria:
Because we compare the numbers in the same column, criteria_range in both cases is the same (C2:C17):
Syntax: =MAXIFS(D2:D17; C2:C17; “>=13”;C2:C17; “<=14”)
Aside from numbers, logical operators can also work with text criteria. In particular, the “not equal to” operator comes in handy when you wish to exclude something from your calculations. For example, to find the tallest student in all sports excluding volleyball, use the following formula:
Syntax: =MAXIFS(D2:D11;B2:B11; “<>”&B20)
Example 5: MAXIFS formulas with wildcard characters (partial match)
To evaluate a condition that contains a specific text or character, include one of the following wildcards in your criteria:
- Question mark (?) to match any single character.
- Asterisk (*) to match any sequence of characters.
For this example, let’s find out the tallest student in game sports. Because the names of all game sports in our dataset end with the word “ball”, we include this word in the criteria and use an asterisk to match any previous characters:
- #VALUE! error – This is returned when the size and shape of the max_range and criteria_rangeN arguments aren’t the same.
- If we are using earlier versions of Excel, we can use an array formula based on MAX and IF to find maximum values with criteria.
- #NAME? error – Occurs when we are using an older function of Excel.
- MAXIFS will include rows that are hidden.