Excel MAXIFS

Article byBabita Sehdev
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

MAXIFS Function in Excel

The 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. For example, we can use MAXIFS with criteria based on dates, numbers, text, and other conditions.

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.

Excel-Maxifs

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 Formula

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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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.

Excel Maxifs Example 1

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.

Excel Maxifs Example 1-1

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.
Excel Maxifs Example 1-2
  • We need to select the range for the second argument where we need to check the criteria.
Excel Maxifs Example 1-3
  • 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.
Excel Maxifs Example 1-4

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.
Excel Maxifs Example 1-5
  • So, the maximum number of marks scored by the female student is 82.
Excel Maxifs Example 1-6
Excel Maxifs Example 1-7

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.

Excel Maxifs Example 2

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.

Excel Maxifs Example 2-1.png

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

Excel Maxifs Example 2-2

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

Excel Maxifs Example 2-3

A drop-down will be created as follows:

Excel Maxifs Example 2-4

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

Excel Maxifs Example 2-5

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.

Excel Maxifs Example 2-6

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:

Create Format Example 3

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

Item Drop down Example 3-1

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.

Region Dropdown Example 3-2

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.

MAX & IF formula Example 3-3.png

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

Keyboard- west Example 3-4

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.

various region Example 3-5

Things to Remember

  1. We must together press the keys Control Shift-Enter in Excel while entering an array formula.
  2. To get the maximum value, we can specify any number of conditions with nested IF functions and MAX.
  3. 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: –