How To Find Cell with The Maximum Value In A Column In Microsoft Excel

In this article, we will learn about how to find the Maximum value if it matches multiple conditions in Excel.

Scenario:

When working with long ranges of data, we need to find the maximum value among the range where more than one condition is matching. In simple words finding out the max value using Excel IF function. IF function returns True or False and Max function looks for the maximum value from the corresponding array.

Syntax to find max with multiple criteria

{=MAX (IF (Criteria1=match1),IF(Criteria2=match2, range_max))}

Note: Use Ctrl + Shift + Enter when working with arrays or ranges in Excel. This will generate Curly Braces on the formula by default. DO NOT try to put this curly braces manually.

Example:

All of these might be confusing to understand. So, let's test this formula via running it on the example shown below. Here we will perform the formula over values with criteria.

Use the formula:

{ =MAX( IF( B2:B11="East", IF( C2:C11 > 50 , D2:D11 )))}

criteria 1 is price must be from the region "East"

criteria 2 is price where quantity is greater than 50.

Explanation:

IF( C2:C11 > 50 , D2:D11 ) returns an array of FALSE values and price values where quantity is greater than 50.

{ FALSE ; 303.63 ; 108.46 ; 153.34 ; FALSE ; 95.58 ; 520.01 ; 90.27 ; 177 ; FALSE }

IF( B2:B11="East", IF( C2:C11 > 50 , D2:D11 )) returns an array of remaining price values where region is East.
MAX function finds the maximum value from the returned array and that would be the required PRICE value.

Here we matched the range (B2:B11) with value "East" and quantity (C2:C11) greater than 50, which returns the maximum from the price range (D2:D11). Press Enter to get the maximum of the range

As you can see we have the MAX value, if criteria match from the range of values.

Alternate function for Excel 365 and 2019 version.

The Excel MAXIFS function is a new function, introduced in Excel 365 and 2019. This function returns the MAX value from a given range.

In this article, we will learn how to use the MAXIFS function in Excel.

Syntax of MAXIFS Function

=MAXIFS(max_range,criteria_range1,criteria1,...)

Max_range1: It is the numeric range that contains the max value.

Criteria_range1: It is the criteria range that you want to filter before getting max value.

Criteria1: It is the criteria or the filter that you want to put on criteria_range before getting the Max value.

You can have multiple pairs of criteria_range and criteria.

Let’s understand the MAXIFS function with an example.

Example of MAXIFS Function

So, here I have a table of some data. The first column contains the numeric values. Second column contains the Region and the third column has the department.

The task is to tell the max value from the first range for each region.

The formula for getting maximum value from "East" region will be:

=MAXIFS(A2:A16,B2:B16,"East")

It will return 82.

Here we have given hardcoded criteria, but we can also give reference of the criteria to make it dynamic.

=MAXIFS($A$2:$A$16,$B$2:$B$16,E2)

Here we have used absolute ranges to lock the max range and criteria range.

You can put more than 1 criteria in MAXIFS function. For example, if I want to get MAX value from IT department of SOUTH region then the formula will be:

=MAXIFS(A2:A16,C2:C16,"IT",B2:B16,"SOUTH")

This will return 100 as per above data. If we put "Accounts" in place of IT, the MAXIFS will return 82.

So this was article about how to the MAXIFS function of Excel 365 and 2019. I hope it was helpful. If you have any doubts regarding this article or any other excel topic, ask in the comments section below.

Related Article

Calculate Max if condition match | To calculate the MAX value with a condition in Excel 2016 and older versions, use this formula. Using the MAX and IF function together we can get a conditional Max output.

Excel 2019/365 Functions:

How to Use The Excel UNIQUE Function | The UNIQUE function returns the Unique values from the given array.

The SORT Function in Excel | The SORT function returns the sorted form of the supplied array.

How to Use Excel SORTBY Function | The SORTBY function sorts a given range by a different specified range. Sorting range does not need to be part of the table.

How to Use The Excel FILTER Function | The FILTER function returns all matched values of given criteria and spills the result into adjacent cells.

The SEQUENCE Function in Excel | The SEQUENCE function returns a series of sequential numbers. It is a dynamic array formula.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.