How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Compute Expert

How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps


Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps



In this tutorial, you will learn how to use MAX IF excel formulas combination. MAX IF can be used to find the largest value from numbers in which data entries fulfill specific criteria.







MAX IF Usefulness

The MAX IF formula is useful if you want to find the largest value from all numbers that fulfill specific criteria.



MAX IF Result

The result of MAX IF is the number with the largest value from numbers in which data entries fulfill specific criteria.



The Excel Version Since Which the Combination of MAX IF Formulas Can Be Used

The MAX and IF formulas can be used since excel 2003.



How to Write MAX IF and Its Inputs

Generally, the way to write the combination of MAX and IF can be illustrated as follows:

{=MAX(IF(criteria_range=criteria[…],number_range))}


The inputs used in the MAX IF writing can be explained as follows:
  • criteria_range = the cell range contains data that you want to evaluate with your criterion
  • criteria = the criterion you want to use to filter the numbers that will be compared to get your largest value
  • […] = optional. The additional cell range and criterion pairs if needed. You should write additional IF formulas or multiply all the criteria cell ranges
  • number_range = The cell range where the numbers which data fulfill your criteria are taken to be compared for the largest value


Maybe you realize there are curly bracket signs ( {} ) that envelops the formula. This sign means that the formula is an array formula.

The array form is needed here because we will use IF to evaluate data in cell ranges. When we use excel formulas that usually have one data input to process data in ranges, an array formula is needed.

This array formula can be activated in excel by pressing the Ctrl + Shift + Enter buttons (Command + Enter on Mac for the excel version before 2016 and Control + Shift + Enter for the excel version 2016 and after) simultaneously after finishing the formula writing.



Another thing to remember for the MAX IF cell ranges inputs is all of them must have the same size. Moreover, they should be in line with each other so it is easy if you want to check the result logic.



The example of Usage and Result

The following will give and explain the usage and result example of MAX IF in excel.

How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of the Example of MAX IF Usage and Result With 1 Criterion

In the example above, we can see how to use the MAX IF formula with one criterion. As illustrated in the previous part, the IF formula needs to be inputted into your MAX formula.

The inputs of the IF itself are the cell range for the criterion evaluation, the criterion, and the numbers cell range. The = sign between the cell range and its criterion represents the relationship symbol that becomes the base of the evaluation process.

In the example, we want to get the largest sales quantity from data entries with the “Orange” goods name. For that, we input the “goods name” cell range with “Orange” as its criterion, connected with the = sign.

The combination of MAX IF formulas is an array formula because we evaluate data in cell ranges in our IF. In the formula process, each data in the criterion cell range will be evaluated by the criterion.

From this evaluation, our IF will produce the TRUE/FALSE logic value for each data entry. For the example above, the comparison result between the criterion and the cell range is approximately like this:

(FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE)


Each entry with the “Apple” goods name will become FALSE and each entry with the “Orange” goods name will become TRUE (because the goods name criterion is “Orange”). From the comparison result, the numbers from the data entries with the TRUE result will be compared by MAX.

How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of the Example of MAX IF Usage and Result With More Than 1 Criteria

The next example is the MAX IF formula writing with more than one criteria. Here, the criteria are “Apple” goods name, week more than or equal to 2, and sales region B. Only the data that fulfill all the criteria, produce TRUE for all of them, will be compared their numbers by MAX.

To input more than one criteria in the MAX IF, you need to use IFs as many as your criteria.

How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of the Example of MAX IF Usage and Result With More Than 1 Criteria (Writing Alternative)

In the third example, we can see the alternative for the MAX IF formula writing with more than one criteria. You can also input the cell ranges and criteria pairs by multiplying them all in your IF.

Because TRUE is 1 and FALSE 0 in excel, then the multiplication will eliminate the data entries which have FALSE. In other words, we will get filtered data entries for their numbers to be compared by MAX.

After writing your MAX IF formula with whichever writing style used above, don’t forget to press Ctrl + Shift + Enter. Those three buttons will change your formula to an array formula. If you just press Enter after writing the MAX IF formula, then the formula won’t function well.



Writing Steps

Here is the detailed explanation of the step-by-step writing of the MAX IF excel formula. Each step will be accompanied by a screenshot to help your learning process.

  1. Type an equal sign ( = ) in the cell where you want to put in your MAX IF result

    How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of Step 1

  2. Type MAX (can be with large and small letters) and input an open bracket sign

    How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of Step 2

  3. Type IF (can be with large and small letters) and input an open bracket sign

    How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of Step 3

  4. Input the cell range where the data you want to evaluate with your criterion is. Then, input an equal sign ( = ) or other signs which represent the data evaluation process in this cell range by your criterion

    How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of Step 4

  5. Input the criterion you want to use to evaluate the data in your previously inputted cell range

    How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of Step 5

  6. Optional: Input other cell range and criterion pairs if needed. There are two ways to input these additional pairs of cell range and criterion:
    • First method: Input a comma sign ( , ) then repeat steps 3 to 5. Repeat the input of comma sign and steps 3-5 until all your cell range and criterion pairs have been inputted


    • How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of Step 6-1

    • Second method: Make a bracket sign which envelops the inputs from step 4 to 5. Then, input a star sign ( * ) then repeat the bracket sign input and steps 4 and 5. Repeat the inputs of star sign, bracket sign, and step 4-5 until all your criteria have been inputted


    • How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of Step 6-2

  7. Input a comma sign and the cell range where the numbers which will be compared to get the largest value are. Later, only the numbers which are in line with the data that pass your criteria will be taken for the comparison

    How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of Step 7

  8. Input close bracket signs with the numbers needed (depending on the open bracket signs that haven’t been closed in this MAX IF formula writing of yours)

    How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of Step 8

  9. Press the Ctrl + Shift + Enter buttons (On Mac, Command + Enter for the excel version earlier than 2016 and Control + Shift + Enter in the excel version 2016 afterward) simultaneously
  10. The process is done!

    How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of Step 10




Alternative: SUMPRODUCT MAX

If you don’t want to use an array formula to get the biggest value that fits your criteria, use SUMPRODUCT MAX. An example of this formula combination writing can be seen in the screenshot below.

How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of the Writing Example of SUMPRODUCT MAX



By using SUMPRODUCT MAX, you don’t need to use an array formula to get your biggest value. The result produced by SUMPRODUCT MAX is similar to the MAX IF result as you can see below.

How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of the Result Example of SUMPRODUCT MAX

The SUMPRODUCT MAX method can succeed because the SUMPRODUCT function multiplies in matrix the numbers between all its cell ranges input. Because TRUE is 1 and FALSE is 0 in excel, SUMPRODUCT will automatically eliminate all data entries that have FALSE. As we know, anything that is multiplied by 0 will become 0 too.



Looking for the N-th Largest Value with Specific Criteria: LARGE IF (or SUMPRODUCT LARGE)

If what you need is the second or third or other nth largest number, then use LARGE IF. The way to use LARGE IF is similar to MAX IF. It is just you need to add the descending order input of the number you want to get from LARGE.

How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of the Writing Example of LARGE IF

How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of the Result Example of LARGE IF

Like the MAX IF, you can also utilize SUMPRODUCT as the alternative to LARGE IF. The way to write SUMPRODUCT LARGE is similar to SUMPRODUCT MAX. Don’t forget to add the descending order input of the largest number you want to get when you use SUMPRODUCT LARGE.

How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of the Writing Example of SUMPRODUCT LARGE

How to Use MAX IF Excel Formula: Usability, Example, and Writing Steps - Screenshot of the Result Example of SUMPRODUCT LARGE



Exercise

After learning how to use MAX IF, now is the time to practice your understanding through this exercise!

Download the exercise file and answer all the questions. Download the answer key file if you have done the exercise and want to check your answers!

Link to the exercise file:
Download here

Questions

Answer in the appropriate gray-colored cells!
  1. What is the largest average score from the eleventh and twelfth class for all subjects? Use MAX IF to answer this question!
  2. What is the largest average score from the tenth and eleventh class in the Biology subject? Use MAX IF to answer this question!
  3. Use SUMPRODUCT MAX to answer question number 2!

Link to the answer key file:
Download here



Additional Note

  • If you want the smallest number with specific criteria, then you just need to change MAX to MIN in your writing. The way to use MIN IF is almost the same as MAX IF which has been explained in this tutorial (or use SUMPRODUCT MIN if you don’t want to use an array formula)
  • If you want the nth smallest number with specific criteria, then just change the LARGE formula to SMALL. The way to use SMALL IF is almost the same as LARGE IF which has been explained previously (or use SUMPRODUCT SMALL if you don’t want to use an array formula)
  • If you have excel version 2019 or above, then you can use the MAXIFS formula for an easier way (don’t need to combine MAX and IF). MAXIFS has the same function as the MAX IF formulas combination




Related tutorials you should learn too:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on




Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert