Max If (Max Value with Condition) – Excel & Google Sheets

This tutorial will demonstrate how to find the largest cell value which meets specific conditions in Excel and Google Sheets.

max if with condition Main Function

Max If Array Function

The MAX Function identifies the largest value in a series of numbers.

We can use the MAX Function combined with an IF Function to identify the largest value that meets a specified condition.

Users of Google Sheets and Excel 2019 or later are recommended to use the simpler MAXIFS Function. This is explained in a later section.

This example will use the MAX and IF Functions in an array formula to identify the largest Order Size for each Store Name

MAX IF Array

In Office 365 and versions of Excel after 2019, you can simply enter the above formula like you normally would (by pressing ENTER).

However, for Excel 2019 and earlier you must enter the formula by pressing CTRL + SHIFT + ENTER. After doing so, you’ll notice curly array brackets appear around the formula.

To show how this formula works, let’s break it down into steps.

This is our final formula (shown without the automatically added array formula brackets):

First, the cell range values are added to the formula as arrays:

Next the Store Name =”A” condition produces an array of TRUE/FALSE values:

Then the IF Function changes all TRUE values into the relevant Order Size:

The MAX Function identifies the largest number in the array, whilst ignoring any FALSE values, to show the largest Order Size for the Store Name = “A”:

Max If – Multiple Criteria

We can also identify the largest value based on multiple criteria by using Boolean logic.

This example will show the largest Order Size for each Store Name, but for Order Dates before 4/30/2021 by using the MAX, IF and DATE Functions:

MAX IF Array Multiple

Notice here we multiply two sets of TRUE/FALSE criteria together:

If both criteria are TRUE then the total condition will calculate as TRUE, but if one (or more) criteria is FALSE it will calculate as FALSE.

Using this methodology, it is possible to add many different criteria to this formula.

Max If – Multiple Criteria with Cell References

Usually, it is not good practice to hard-code values into formulas. Instead, it’s more flexible to use separate cells to define the criteria.

To match the Store Name to the value shown in column F, we can update the formula to be:

MAX IF Array Multiple Ref

Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:

But these formulas will not work properly when copy and pasted elsewhere in your file. Instead, you should use locked cell references like this:

Read our article on Locking Cell References to learn more.

MAXIFS Function

Users of Google Sheets and Excel 2019 or later can use the single MAXIFS Function to replicate the behavior of the MAX and IF Functions shown in the earlier examples.

This next example uses the MAXIFS and DATE Functions to show the largest Order Size for each Store Name for Order Dates before 4/30/2021:

MAXIFS

The MAXIFS Function does not require the user to press CTRL + SHIFT + ENTER when entering the formula.

Max If (Max Value with Condition) in Google Sheets

The examples shown above work exactly the same in Google Sheets as in Excel, but as the MAXIFS Function is available, it is recommended to use this single function instead of combining the MAX and IF Functions.

If the examples using MAX and IF Functions are needed to be used, then Google Sheets requires that you enter these as array formulas. Instead of showing the formula with Excel curly array brackets { }, pressing CTRL + SHIFT + ENTER automatically adds the ARRAYFORMULA Function around the formula:

max if with condition Google Function

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!