In this post, you’ll learn about the MAXIFS Function, its syntax and the way of using the MAXIFS Function in Microsoft Excel.
What is MAXIFS Function?
MAXIFS Function in excel is a statistical function. The MAXIFS Function calculates and returns the largest numeric value that meets one or more criteria in a given range.
MAXIFS Function can also be used with criteria based on dates, numbers, text, and other conditions. MAXIFS supports logical operators(>, <, <>, =) and wildcards(*, ?) for partial matching.
In financial analysis, the MAXIFS Function is useful in extracting a maximum numeric value.
Syntax
=MAXIFS (max_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)
Argument:
- = – built-in function.
- MAXIFS () – function name.
- max_range – range of values determines the maximum values.
- range 1(required value),range 2[optional] – the range of arguments to calculate.
- criteria 1(required argument), criteria 2[optional] – is the criteria to use in range.
Criteria can be number, expression, or text that defines which cells will evaluates as a maximum value.
MAXIFS Function is in a group of eight functions in Excel that splits logical criteria into two parts(range + criteria).
Notes:
- Conditions applied using range or criteria pairs.
- MAXIFS can handle up to 126 range or criteria pairs.
- #VALUE Error – if criteria range is not the same size as the max_range.
- MAXIFS Function returns 0, if no cells matches the criteria.
- Ignores empty cells, even when criteria match.
REMARK:
MAXIFS Function is a new function, available in Excel 365 and Excel 2019 but in earlier versions you have to use an array formula MAX and IF to find the maximum values with criteria.
How to use MAXIFS Function in Excel ?
MAXIFS Function calculates largest numeric values with one or more criteria.
DOUBLE QUOTES (“”):
- Text values are must be within double quotes, but numbers are not. If the logical operator with a number, the number and operator must be within double quotes.
- Value from another cell – value from another cell by using catenation.
- WILLCARDS:
The wildcards characters question mark (?), asterisk (*), or tilde (~) can be used in criteria.
- The question mark (?) matches any single characters.
- The asterisk (*) matches any sequences of characters.
- The tilde (~) is an escape character it allows you to find the literal wildcard.
Example:
Using MAXIFS Function we can calculate the maximum score of 2nd years among the batch of 1st, 2nd and 3rd-year students
Step 1:
Open the workbook in your Microsoft Excel.
Step 2:
Enter the data, in the workbook.
Now we entered the student’s name with the year of studying and the total marks secured in their exams.
STEP 3:
In the new cell, give the syntax. Start with ‘ =’ for every functions, MAXIFS to initiate functions name, followed by open parenthesis.
According to the arguments of the syntax, you have to calculate the max_range. Therefore the max_range value is from D3:D12.
According to the example, the max_range is the score of the students.
STEP 4:
After calculating max_range, next step is to calculate criteria_range 1.
criteria_range 1 is from C3:C12.
According to the example, criteria_range 1 is year of students.
STEP 5:
After calculating criteria, next step is to calculate criteria 1.
criteria 1 is 2nd year.
STEP 6:
Press Enter to get the MAXIFS value.
Return 99. The maximum value among all the 2nd year students is 99.
