Excel MAXIFS

MAXIFS Function in Excel

MAXIFS function of Excel is used to get the maximum values based on various criteria. Sometimes, we have to analyze the data based on criteria. We can use MAXIFS with criteria based on dates, numbers, text and other conditions.

We can use all types of logical operatorsLogical Operators In Excel, logical operators, also known as comparison operators, are used to compare two or more values. Depending on whether the condition matching is true or false, these operators return the output.read more like >,<,>=,<=,<>,=, and wildcards like *,? for partial matching. The condition “B?e” will match all cells containing a text string beginning with “A” and ending in “e” and there should be only one character in between.

The condition “H*e” will match all cells containing a text string beginning with “H” and ending with “e” with multiple characters in between. Finding out maximum marks for girls and boys separately for a class is one of the situations where this function can be used.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel MAXIFS (wallstreetmojo.com)

Syntax

The formula for this function is:

MAXIFS function returns the maximum value from a range based on multiple criteria to check in multiple ranges.

Arguments Explanation

Max Range: This is the range from where we need to find out the maximum value.
Range 1: This is the range from which we will compare the values with the criteria to get maximum value.
Criteria 1: This is the argument for the condition to specify.
Range 2: This is another range in which we will compare the values with the criteria to get maximum value. In this way, we can provide 126 pairs of range/criteria.
Criteria 2: This is the argument for the second condition to specify.

How to Use MAXIFS Function in Excel?

Let us understand the MAXIFS function with some examples in Excel.

You can download this MAXIFS Function Excel Template here – MAXIFS Function Excel Template

Example #1

Suppose we have the following data of students of a class where we have marks scored by students with their gender details.

For awarding students, we need to find the maximum marks for females and males separately.

To find, the steps would be as follows:

We have created the following format for displaying maximum marks.

Now, to get the maximum marks for female and male students, we will apply the MAXIFS formula.

As we know the syntax, we will refer to the cells where marks are written for the first argument.

Now we need to select the range for the second argument where we need to check the criteria.

For the third argument, we will refer to the cell to the left of the formula cell. A left cell contains ‘Female,’ which is the gender which we need to compare first and then get the maximum marks.

Now we will close the bracket for the function and will get the maximum marks scored by a female student.

As we can see in the above function, we have used a relative cell reference in excel for all three arguments, which means that if we copy and paste the function in another cell, then the reference will get changed. If we want to keep the reference the same, then we need to use absolute reference.

To make reference absolute, we will use the ‘$’ sign before the column alphabet and row number.

So, the maximum marks scored by Female student is 82.

Now we will copy the formula and paste it into the cell for male students. Make sure we use ‘Paste Formula Only’ of ‘Paste SpecialPaste Special Paste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more‘ as we do not want to copy the formatting too.

We have got maximum marks scored by a male student also, which is 91.

Example #2

In the earlier version of Excel, we do not have MAXIFS function as a built-in function. In that case, we were using an array formula based on MAX and IF Function.

Suppose we have the following data for an organization.

In this data, we can see that various items have been sold by the salesperson in different regions. If we want to find the maximum quantity of an item sold, then we can use an array formula based on MAX and IF Function.

Steps:

We need to create drop-down for item names so that the user can select from the list; he will not have to type as there can be spelling error while typing. To create a drop-down list in excel, we need to first create the format.

To create a drop-down, we will select the cell and then click on the ‘Data Validation in ExcelData Validation In Excel Data validation in Excel is used to limit a user's inputs to specified cells or input ranges by offering pre-defined inputs for selection to avoid multiple redundant data inputs.read more‘ command available in the ‘Data Tools’ group in the ‘Data.’

Choose ‘List’ for the ‘Allow’ option, type the item names in the ‘Source’ text box, and click on ‘OK.’

A dropdown will be created as follows:

Now to get maximum sales, we will apply the formula as follows:

While entering the formula, we need to press Ctrl+Shift+Enter as this is an array formula.

As we have given a range of multiple cells for ‘logical_test’ for IF function and have compared the same with Item Name written in the above cell, the IF function in excel will return more than one values, and then the MAX function will take out the maximum value. As the IF function is returning more than one value, that is why it is called an array formula in excel.

Example #3

Suppose we want to find out maximum sales for an item based on the region too. To do the same, we will use the NESTED IF formula with MAX function.

Steps would be:

First, we need to create the format as follows:

We need to create drop-down for ‘Item Name’ and ‘Region.’

Now we will apply the array formula based on NESTED IF in ExcelNESTED IF In Excel In Excel, multiple IF conditions are IF statements that are contained within another IF statement. They are used to test multiple conditions at the same time and return distinct values. Additional IF statements can be included in the 'value if true' and 'value if false' arguments of a standard IF formula.read more and MAX function.

First, we need to compare item names with value written in ‘G7’ and then region with the value written in ‘G8’. IF functions will return values for a particular item and region. MAX function will return the maximum value.