WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel MAXIFS

Excel MAXIFS

By Babita SehdevBabita Sehdev | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

Excel Maxifs.png

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

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

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 Excel‘ 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 Excel 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! Error.

Recommended Articles

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 –

  • Excel Commands
  • Carriage Return in Excel
  • Use Max and IF in Excel
  • SUMIF Not Blank Cells
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download MAXIFS Function Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More