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

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

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.

Excel Maxifs Example 1

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.

Excel Maxifs Example 1-1

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.
Excel Maxifs Example 1-2
  • Now 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

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

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.

Excel Maxifs Example 2

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.

Excel Maxifs Example 2-1.png

To create a drop-down, we will select the cell and then click on the Data Validation in ExcelData Validation In ExcelData 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.’

Excel Maxifs Example 2-2

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 dropdown will be created as follows:

Excel Maxifs Example 2-4

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

Excel Maxifs Example 2-5

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.

Excel Maxifs Example 2-6

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:

Create Format Example 3

We need to create 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 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.

MAX & IF formula Example 3-3.png

The maximum sales of Keyboard in the west region are 34.

Keyboard- west Example 3-4

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

Now we will select item names and regions to find out maximum sales.

various region Example 3-5

Things to Remember

  1. While entering an array formula, press Control Shift-Enter in Excel together.
  2. With Nested IF functions and MAX, we can specify any number of conditions to get the maximum value.
  3. The size and shape of the max_range and criteria_rangeN 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 has been a guide to Excel MAXIFS. Here we discuss how to use the MAXIFS function to get maximum value based on various criteria in excel along with examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>