MAXIFS Function in Excel

For example, finding out maximum marks for girls and boys separately for a class is one of the situations where we can use this function.

We can use all types of logical operatorsLogical OperatorsLogical operators in excel are also known as the comparison operators and they are used to compare two or more values, the return output given by these operators are either true or false, we get true value when the conditions match the criteria and false as a result when the conditions do not match the criteria.read more like >,<,>=,<=,<>,=, and wildcards like *,? for partial matching. For example, 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.

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

Syntax

The formula for this function is:

The 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 the maximum value.Range 1: This is the range from which we will compare the values with the criteria to get the 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 the 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.

Example #1

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

We need to find the maximum marks for females and males separately for awarding students.

To find that, we must follow the below steps:

We have created the following format for displaying maximum marks.

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

  • We will refer to the cells where marks are written for the first argument as we know the syntax.

  • 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.

We will close the bracket for the function and 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 into another cell, then the reference will get changed. So if we want to keep the reference the same, we need to use absolute reference.

  • We will use the ‘$’ sign before the column alphabet and row number to make reference absolute.

  • So, the maximum number of marks scored by the female student is 82.

  • We will copy the formula and paste it into the cell for male students. We use “Paste Formula Only” from ‘Paste SpecialPaste SpecialPaste 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.

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

Example #2

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

Suppose we have the following data for an organization.

In this data, we can see that the salesperson has sold various items in different regions. Therefore, we can use an array formula based on MAX and IF functions if we want to find the maximum quantity of an item sold.

Steps:

We need to create a drop-down for item names so that the user can select from the list. In addition, he will not have to type as there can be spelling errors. To create a drop-down list in excelDrop-down List In ExcelA drop-down list in excel is a pre-defined list of inputs that allows users to select an option.read more, we need to make the format.

We will select the cell to create a drop-down and then click on the ‘Data Validation in ExcelData Validation In ExcelThe data validation in excel helps control the kind of input entered by a user in the worksheet.read more‘ command available in the “Data Tools” group in the “Data.”

We must choose “List” for the ‘”Allow” option, type the item names in the “Source” text box, and click on “OK.”

A drop-down will be created as follows:

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

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

As we have given a range of multiple cells for ‘logical_test’ for the 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 value, and then the MAX function will take out the maximum value. As the IF function returns more than one value, it is called an array formula in excelArray Formula In ExcelArray formulas are extremely helpful and powerful formulas that are used in Excel to execute some of the most complex calculations. There are two types of array formulas: one that returns a single result and the other that returns multiple results.read more.

Example #3

Suppose we want to find maximum sales for an item based on the region. We will use the NESTED IF NESTED IF In Excel, nested if function means using another logical or conditional function with the if function to test multiple conditions. For example, if there are two conditions to be tested, we can use the logical functions AND or OR depending on the situation, or we can use the other conditional functions to test even more ifs inside a single if.read more formula with the MAX function to do the same.

Steps would be:

First, we need to create the format as follows:

Next, we need to create a drop-down for “Item Name” and “Region.”

Now, we will apply the array formula based on NESTED IF in ExcelNESTED IF In ExcelIn 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 the value written in “G7” and then the region with the value written in “G8.” The IF functions will return values for a particular item and region. The MAX function will return the maximum value.

The maximum sales of “Keyboard” in the “West” region are 34.

Ensure that we press Ctrl+Shift+Enter while entering the formula as this is an array formula.

Now, we must select item names and regions to find maximum sales.

Things to Remember

  • We must together press the keys Control Shift-Enter in Excel while entering an array formula.To get the maximum value, we can specify any number of conditions with nested IF functions and MAX.The size and shape of the max_range and criteria_range N arguments must be the same. Otherwise, MAXIFS functions return the #VALUE! ErrorReturn The #VALUE! Error#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error.read more.

This article has been a guide to Excel MAXIFS. Here, we discuss using the MAXIFS function to get maximum value based on various criteria in Excel and examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Excel CommandsCarriage Return in ExcelUse Max and IF in ExcelSUMIF Not Blank Cells