In this post, *you’ll learn about the MAXIFS Function, its syntax and the way of using the MAXIFS Function in Microsoft Excel.*

**Wh**at 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**.

**S**yntax

=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____:__

__STEP 5__

After calculating criteria, next step is to calculate** criteria 1**.

criteria 1 is** 2nd year**.

__STEP 6:__

__STEP 6:__

Press Enter to get the **MAXIFS value**.

Return **99**. The **maximum** value among all the 2nd year students is 99.

## Leave a Review