Finding the Maximum Value with Condition – Excel and Google Sheets

In this juicy article, we’ll show you how to find the largest value that meets specific conditions in both Excel and Google Sheets. Get ready to uncover all the secrets to master this incredible feature!

The MAXIFS Function

Users of Google Sheets and Excel 2019 (or later versions) can leverage the unique MAXIFS function. This function allows you to find the maximum value while respecting specific conditions.

Here’s an example that uses the MAXIFS and DATE functions to find the largest “Order Size” for each “Store Name” before 30/04/2021:

=MAXIFS(D3:D8, B3:B8, "A", C3:C8, "<" & DATE(2021,4,30))

One of the noteworthy features of the MAXIFS function is that it doesn’t require you to press CTRL + SHIFT + ENTER when entering the formula.

The Max If (Array Formula) Function

If you don’t have access to the MAXIFS function, you can create an array formula to simulate its functionality.

The MAX function identifies the largest value in a series of numbers. By combining this function with the IF function, you can identify the maximum value that meets a specified condition.

Here’s an example that uses the MAX and IF functions in an array formula to find the largest “Order Size” for each “Store Name”:

{=MAX(IF(B3:B8="A",D3:D8))}

In Office 365 and Excel versions after 2019, you can simply enter the formula as usual (by pressing ENTER). However, for Excel 2019 and earlier versions, you need to enter the formula by pressing CTRL + SHIFT + ENTER. You’ll notice that curly braces appear around the formula, indicating it’s an array formula.

To understand how this formula works, let’s break it down step by step:

=MAX(IF(B3:B8="A",D3:D8))

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

=MAX(IF({"A"; "B"; "A"; "B"; "A"; "B"}="A",{500; 400; 300; 700; 600; 200}))

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

=MAX(IF({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE},{500; 400; 300; 700; 600; 200}))

Then, the IF function transforms all the TRUE values into the corresponding “Order Size”:

=MAX({500; FALSE; 300; FALSE; 600; FALSE})

The MAX function identifies the largest number in the array, ignoring the FALSE values, and displays the largest “Order Size” for the “Store Name” = “A”:

=600

Try our AI Formula Generator

Max If – Multiple Criteria

It’s also possible to identify the largest value using multiple criteria using Boolean logic.

This example shows how to find the largest “Order Size” for each “Store Name”, but only for “Order Dates” before 30/04/2021 using the MAX, IF, and DATE functions:

{=MAX(IF((B3:B8="A")*(C3:C8<DATE(2021,4,30)),D3:D8))}

Notice that we’re multiplying two sets of TRUE/FALSE criteria together:

(B3:B8="A") * (C3:C8<DATE(2021,4,30))

If both criteria are TRUE, then the overall condition evaluates to TRUE. If any (or all) of the criteria are FALSE, it evaluates to FALSE.

Using this methodology, you can add multiple different criteria to this formula.

Max If – Multiple Criteria with Cell References

In general, it’s best not to hard-code values in formulas. It’s more flexible to use separate cells to define the criteria.

To match the “Store Name” with the value specified in column F, we can update the formula as follows:

{=MAX(IF((B3:B8=F3)*(C3:C8<DATE(2021,4,30)),D3:D8))}

Locking Cell References

For the sake of readability, we’ve shown the formulas without locked cell references:

{=MAX(IF((B3:B8=F3)*(C3:C8<DATE(2021,4,30)),D3:D8))}

However, these formulas won’t work correctly if they’re copied and pasted elsewhere in your file. Therefore, you should use locked cell references like this:

{=MAX(IF(($B$3:$B$8=F3)*($C$3:$C$8<DATE(2021,4,30)),$D$3:$D$8))}

Read our article on locking cell references to learn more.

Max If (Max Value with Condition) in Google Sheets

The examples presented above work exactly the same way in Google Sheets as they do in Excel. However, since the MAXIFS function is available, it’s recommended to use this unique function instead of combining the MAX and IF functions.

If you still want to use the array formula MAX/IF in Google Sheets, you need to enter the formula within the ARRAYFORMULA function (or simply press CTRL + SHIFT + ENTER, and Google Sheets will add this function for you):

=ARRAYFORMULA(MAX(IF((B3:B8="A")*(C3:C8<DATE(2021,4,30)),D3:D8)))

Now that you have all the necessary information to use the MAX IF function in Excel and Google Sheets, you can unlock the full potential of these powerful tools!

Don’t forget to visit Crawlan.com for more tips and tricks on using Google Sheets and other digital tools.

Prepare to be amazed by your own data manipulation skills!