# MAXIFS Function in Excel

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.