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 operators 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.
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 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.
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 ‘$’ sign before 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 Special‘ 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 drop-down, we will select the cell and then click on the ‘Data Validation in Excel‘ 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 MAX function will take out the maximum value. As IF function is returning more than one value, that is why it is called 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 Excel 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.
The maximum sales of Keyboard in the west region are 34.
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.
Things to Remember
- While entering an array formula, press Control Shift Enter in Excel together.
- With Nested IF functions and MAX, we can specify any number of conditions to get the maximum value.
- The size and shape of the max_range and criteria_rangeN arguments must be the same, otherwise, MAXIFS functions return the #VALUE! Error.
Recommended Articles
This has been a guide to Excel MAXIFS. Here we discuss how to use MAXIFS function to get maximum value based on various criteria in excel along with examples and downloadable excel template. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
