EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel Excel MAX IF Function
Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course

Excel MAX IF Function

By Karthikeyan SubburamanKarthikeyan Subburaman

MAX IF Function in Excel

Excel MAX IF Function (Table of Contents)

  • MAX IF in Excel
  • How to Use MAX IF Function in Excel?

MAX IF in Excel

Max function and IF function in Excel are used to find the maximum value from the given range of datasets with the defined criteria. The MAX function returns the maximum value from the select number range, and the IF function helps find that number with chosen criteria. For this, we can first apply the IF function where, as per syntax, first choose Criteria Range and with the criteria we want, then select the value range. Once done, then cover the complete IF function in the MAX function’s syntax with curly brackets.

In Microsoft Excel, there is no such separate syntax for the MAXIF function, but we can combine the MAX and IF function in an array formula.

MAX Formula in Excel

Below is the MAX Formula in Excel:

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,725 ratings)

MAX Formula in Excel

Arguments of MAX Formula:

  • number1: This number1 refers to a numeric value or range which contains a numeric value.
  • number2 [optional]: This number2 refers to a numeric value or range which contains a numeric value.

IF Formula in Excel

Below is the IF Formula in Excel:

IF Formula in Excel

Arguments of IF Formula:

  • Logical test: Which is a logical expression value to check the value is TRUE or FALSE.
  • value _if_ true: Which is a value that returns if the logical test is true.
  • value _if_ false: Which is a value that returns if the logical test is false.

How to Use MAX IF Function in Excel?

MAX and IF Functions in Excel is very simple and easy to use. Let’s understand the working of the MAX IF Function in Excel with some example.

You can download this MAX IF Function Excel Template here – MAX IF Function Excel Template

Example #1 –  MAX Function in Excel

In this example, we will first learn how to use the MAX function with the below example.

Consider the below example, which shows sales data of the product category wise.

Example 1

Now we will use the MAX function to find out which has the largest sales value by following the below steps.

Step 1 – First, select the new cell, i.e. D11.

Step 2 – Apply the MAX function i.e. =MAX(D2:D9)

MAX function

Step 3 – Press enter so that we will get the MAX value is 1250, which is shown in the below screenshot.

In the below result, the Max function will check for the largest number in the D column and return the maximum sales value as 1250.

Result of Example 1

Example #2 – Using MAX IF Function in Excel with an array formula

In this example, we will learn how to use MAX IF function in Excel. Assume that we need to check the largest sales value category wise. Using only the max function, we can check only the maximum value, but we cannot check the maximum value category wise if the sales data contain a huge category and sales value.

In such a case, we can combine MAX and If Function with an array formula so that it makes the task very easier to find out the largest sales value category wise in Excel.

Consider the below example, which has Product Name, Category and Sales value which is shown below.

Example 2

Now we will apply the Excel MAX IF function to find out the maximum sales value by following the below steps.

Step 1 – First, we will create a new table with a category name to display the result, as shown below.

MAX IF Function Example 2-2

Step 2 – Select cell G4.

MAX IF Function Example 2-3

Step 3 – Apply the MAX IF formula i.e. =MAX(IF(C$2:C13=F4,D2:D13))

MAX IF Function Example 2-4

In the above formula, we have applied MAX and IF function and then we selected Column C2 to C13 where we have listed the category name as C2:C13 and then we have checked the condition by selecting the cell as F4 where the F4 column is nothing but Category Name.

Now the if condition will check for the entire category name in C2 to C13 column whether it matches with the mentioned Category name in the F4 column; if the category name matches, it will return the largest sales value from the selected D column as D2 to D13 and lock the cells by pressing F4 where we will get the $ symbol to indicate that cells are locked, and we have given the Formula as =MAX(IF($C$2:$C$13=F4,$D$2:$D$13)).

MAX IF Function Example 2-5

After the MAXIF function is completed by closing all the brackets now, hold SHIFT+CTRL and then press enter as SHIFT+CTRL+ENTER so that we will get the output in array formula where we will get open and closing parenthesis at the opening and end of the statement, which is shown in the below screenshot.

MAX IF Function Example 2-6

Here as we can see in the above screenshot MAX IF function check for the category name in the D column as well as in the F column, and if it matches, it will return the largest value; here in this example, we got the output as 330 which is a largest sales value in the category.

Now drag down the MAX IF formulas for all the cell so that we will get the output result which is shown below.

Result of Example 2

Example #3 – Using MAX IF Function in Excel with an array formula

In this example, we will use the same Excel MAX IF function to find out the highest marks from a set of students.

Consider the below example, which shows student name along with their Gender and Marks, which is shown below.

Example 3

Here in this example, we are going to find out Gender wise who has scored the highest marks by following the below steps.

Step 1 – First, create a new table with Male and Female as a separate column to display the result, which is shown below.

MAX IF Function Example 3-2

Step 2 – Now, we will apply the Excel MAX IF function as follows. Select cell D11.

MAX IF Function Example 3-3

Step 3 – Insert the MAX IF function by selecting the Gender column from C2 to C9.

Now we have to give the condition as IF(C2:C9=C11,D2:D9), which means that the IF condition will check for the gender from the C2 column whether it matches with C11 and If the gender matches MAX IF function returns the highest value.

Step 4 – Now lock the cells by pressing F4 so that the selected cells will be locked, which is shown in the below screenshot.

MAX IF Function Example 3-4

Step 5 – Once we complete the MAXIF function, Hit SHIFT+CRL+ENTER to make it an array formula to see the opening and closing parenthesis in the MAX IF function, which is shown below.

MAX IF Function Example 3-5

We can see that we got the highest mark as 95 for the gender MALE in the above screenshot.

Step 6 – Drag down the formula for all the below cells so that we will get the output as shown below.

Result of Example 3

In the above result using the Excel MAX IF function, we have found the highest marks gender-wise where MALE has scored 95 as the highest mark, and for FEMALE, we got 93 as the Highest mark.

Things to Remember

  • In Microsoft Excel, there is no such MAX IF function as SUMIF and COUNTIF.
  • We can use the MAX and IF function by combining them in the formulation.
  • MAX IF function will throw an error #VALUE! If we did not apply the SHIFT+ENTER +KEY, make sure that you are using MAX IF function as an array formula.

Recommended Articles

This has been a guide to MAX IF in Excel. Here we discuss how to use MAX IF Function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

  1. MAX Excel Function
  2. SUMIF in Excel
  3. Excel SUM MAX MIN AVERAGE
  4. MAX Formula in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for Marketing Training (8 Courses, 13+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download MAX IF Function Excel Template

EDUCBA

Download MAX IF Function Excel Template

EDUCBA

डाउनलोड MAX IF Function Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more