This post will guide you how to** find the maximum value in a range of cells based on single or multiple criteria** in Excel. How do I calculate the Minimum value based on criteria with MIN Function in Excel.

Table of Contents

- 1. Find Max Value based on Single Criteria
- 2. Find Max Value based on Multiple Criteria
- 3. Find Min Value based on Single Criteria
- 4. Find Min Value based on Multiple Criteria
- 5. Find Max Value based on Multiple Criteria Using VBA Code
- 6. Video: Find Max or Min Value based on Multiple Criteria
- 7. Related Functions

## 1. Find Max Value based on Single Criteria

Assuming that you have a list of data in range A1:B5, in which contain product names and sale values. And you want to find the maximum value of product name “excel” in range A1:B5. You can use an Excel Array formula based on the MAX function. Like this:

**=MAX((A2:A5="excel")*B2:B5)**

Then you need to type this formula into a blank cell, and press **Ctrl + Shift + Enter** keys to change it as array formula. the Maximum value of product “excel” would be returned.

You can also use another array formula based on the MAX function and the IF function to achieve the same result. Like this:

**=MAX(IF(A2:A5="excel",B2:B5))**

## 2. Find Max Value based on Multiple Criteria

Assuming that you want to find the maximum value of product “excel” from the month of Jan in range A1:D5, you can use the following array formula based on the MAX function and the IF function to find the max value based on multiple criteria. Like this:

**=MAX(IF(A2:A5="Jan", IF(B2:B5="excel",D2:D5),))**

Then you need to type this formula into a blank cell, and press **Ctrl + Shift + Enter** keys to change it as array formula. the Maximum value of product “excel” in the month of Jan would be returned.

## 3. Find Min Value based on Single Criteria

If you want to find the minimum value of the product “excel” from the range A1:B5, you can use the following formula based on the MIN function:

**=MIN(IF(A2:A5="excel",B2:B5))**

## 4. Find Min Value based on Multiple Criteria

If you want to find the minimum value of the product “excel” from the month of Jan in range A1:D5, you can use the following array formula based on the MIN function and the IF function to find the min value based on the multiple criteria. Like this:

**=MIN(IF(A2:A5="Jan",IF(B2:B5="excel",D2:D5)))**

## 5. Find Max Value based on Multiple Criteria Using VBA Code

Now, let’s explore the another method, where we’ll use VBA code to find the maximum value based on multiple criteria. This method provides more flexibility and control, especially for complex scenarios where formulas may not suffice.

Press Alt + F11 to open the Visual Basic for Applications editor.

In the VBA editor, go to Insert and select Module to insert a new module.

Copy and paste the following VBA code into the module

```
Function MaxIfAndIf(rngA As Range, rngB As Range, rngD As Range, valueA As String, valueB As String) As Variant
Dim cellA As Range
Dim cellB As Range
Dim cellD As Range
Dim maxVal As Variant
maxVal = Empty ' Initialize maxVal as Empty
' Ensure that all three ranges have the same number of rows
If rngA.Rows.Count <> rngB.Rows.Count Or rngA.Rows.Count <> rngD.Rows.Count Then
MaxIfAndIf = CVErr(xlErrValue) ' Return an error value if the ranges don't match
Exit Function
End If
' Loop through the range in rngA
For Each cellA In rngA
If cellA.Value = valueA Then
Set cellB = cellA.Offset(0, rngB.Column - rngA.Column) ' Use Offset to ensure correct column offset for rngB
If cellB.Value = valueB Then
Set cellD = cellA.Offset(0, rngD.Column - rngA.Column) ' Use Offset to ensure correct column offset for rngD
If IsNumeric(cellD.Value) Then ' Check if cellD contains a numeric value
If IsEmpty(maxVal) Or cellD.Value > maxVal Then
maxVal = cellD.Value ' Update maxVal if a larger value is found
End If
End If
End If
End If
Next cellA
' Check if a valid maximum value was found
If IsEmpty(maxVal) Then
MaxIfAndIf = CVErr(xlErrValue) ' Return an error value if no valid maximum was found
Else
MaxIfAndIf = maxVal ' Return the found maximum value
End If
End Function
```

Close the VBA editor window by clicking the close button (X) or pressing ALT + Q. This will return you to the Excel workbook.

In a cell, enter the formula:

`=MaxIfAndIf(A2:A5, B2:B5, D2:D5, "Jan", "excel")`

To use this function, you need to call it with four arguments: the ranges for criteria A, B, and D, as well as the specific values for criteria A and B. This function will then return the maximum value from range D that satisfies both criteria A and B.

## 6. Video: Find Max or Min Value based on Multiple Criteria

This Excel video tutorial, we’ll explore two methods to find the maximum value based on multiple criteria. We’ll start by using a formula based on MAX and IF functions, followed by leveraging VBA code for more complex scenarios. Let’s explore each method.

## 7. Related Functions

- Excel MIN function

The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])…. - Excel MAX function

The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])… - Excel IF function

The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

## Leave a Reply

You must be logged in to post a comment.