Formulas to Get Maximum Value in a Range with Conditions in Excel!

Ads

Listen to this article

In Excel, how could we extract the maximum value based on one or more criteria? There is a formula with MAX and IF Functions in Excel. By applying it to the worksheet, we can easily extract the maximum value of the specified range based on the given conditions.

On that note, the MAX Function helps to find the highest value in an array and the IF function helps to apply conditions as per your preference. This article brings you some tricky tips to find out the maximum value based on criteria in Excel. Get an official version of MS Excel from the following link:

https://www.microsoft.com/en-in/microsoft-365/excel

General Formula

Here we come up with a simple formula to get the highest value in the specified range based on some conditions in the spreadsheet.

=MAX(IF(range=criteria, values))

Syntax Explanations

MAX – The MAX Functio n helps to return the largest numeric value from the range of input values.
IF – This function will return one value for a TRUE result, and another for a FALSE result. Read more on the IF Function.
Values – It represents the input values from the given worksheets.
Comma symbol (,) – It is a separator that helps to separate a list of values.
Parenthesis () – The main purpose of this symbol is to group the elements.
Criteria – It is the specific month that wants to find the maximum value.
Range – It represents the input ranges that contain names.

How to Apply Formula to Get Maximum Value in a Range?

In order to count the highest value in the range, we can use an Excel formula. This formula can make use of the MAX and IF functions. The MAX function will help you get the highest value from the group of values in the worksheet, whereas, the IF function is used to apply conditions. For instance, we will need to get the highest score of Jacob from the student’s mark list.

Use the below-mentioned formula in any cell where you want to get the output result.

=MAX(IF(B3:B11=G2,D3:D11))

Once you have entered the formula, click on the Enter button to execute it and get the result.

If the above-given scenario is not enough to understand this formula and want to learn this with a simple example, kindly move to the following section.

Alternate Example

Here we come up with a simple example to know how to apply this Excel formula in the spreadsheet.

In the below example image, you can see the list of fruits and their sales prices based on the month. And we want to get the highest sales price of the fruit “Apple.”

Then, enter the given formula in a cell where you want to get the result.

=MAX(IF(B3:B11=G2, D3:D11))

Finally, you will need to click the Enter button to get the result, as shown in the below screenshot.

From this example, you can easily understand how to get the largest value in the range with conditions.

Closure

From this tutorial, you can easily understand the formula to get the maximum values with criteria in Excel. If you have any doubt regarding this article or have any other questions related to Excel, let me know in the comments section below. For more updates keep in touch with our website AawExcel and Excel Formulas!