In various calculations, we may need to calculate maximum values from any given dataset. Excel provides its built-in function which is the **MAX **function which returns the largest value from the passed value. Sometimes we may need to find the largest value based on criteria or conditions. In this article, I will show how to find maximum value in Excel with conditions. Here I will be using **MAX**, **IF**, **SUMPRODUCT**, **MAXIFS **functions.

**Table of Contents**hide

## Download the Practice Workbook

**5 Ways to Find Maximum Value in Excel with Condition**

**1. Find Maximum Value with Condition Using MAX Function**

In this section, we will be using the **MAX **function. Let’s see the concept of this function first.

`MAX(number1, [number2], ...)`

In this function’s parameter, we can pass the numbers and it will search or figure out the maximum possible number from them and give it as an output. 1 to 255 numbers for which you want to find the maximum value. For more information, you visit this Link

Let’s assume we have a dataset of some students with their ID, Names, Department, Semester, CGPA, and Fees. Our task is to find out the maximum fees for any specific semester.

**Step 1: **Enter the formula in cell **J4 **and press **Ctrl+Shift+Enter **

`=MAX((F4:F16=K3)*H4:H16)`

**Formula Explanation**

Here we are using an array formula. Firstly, using **(F4:F16=K3) **part we are searching the semester matched with our entered semester. Then the full max function searches the maximum number among the selected cells.

**Step 2: **Enter any semester number in cell **J3 **and press **Enter**

**2. Find Maximum Value with Condition Using MAX IF Formula**

Let’s see how to find out the maximum number with conditions like the previous one but here we will be using MAX IF functions in the formula. Lett’s see the syntax of the **IF** function

`IF (logical_test, [value_if_true], [value_if_false])`

In the first portion it takes the condition or criteria, then the value which will be printed if the result is true and then if the result is false.

For this process, we will find the maximum CGPA for a specific department.

**Step 1: **Enter the formula in cell J4 and press **Ctrl+Shift+Enter**

`=MAX(IF(D4:D16=J3, F4:F16))`

**Formula Explanation**

**IF(D4:D16=J3, F4:F16) **This portion matches the cells from the Department with our entered data in cell J3. Also finds the matched cells CGPA and then the MAX function finds the highest value from them.

**Step 2: **Enter any **Department **name in cell **J3 **and press **Enter**

**3. Using MAX IF without Array**

In this section let’s see how we can do the same task done in method 2 without an array formula. For this, we will need the **SUMPRODUCT **function.

`SUMPRODUCT(array1, [array2], [array3], …)`

It takes the array in its parameter and returns the sum. In this function, we can pass multiple arrays to get the total sum product. For more details, you can visit this Link

Again, our dataset will be the same as above. But we add one more. So totally our task is to find the largest CGPA for any specific Department and the Semester should be below our entered number.

**Step 1: **Enter the formula in cell **J5**

`=SUMPRODUCT(MAX(((D4:D16=J3)*(E4:E16<J4)*(F4:F16))))`

**Formula Explanation**

** MAX(((D4:D16=J3)*(E4:E16=J4)*(F4:F16))) **This portion matched data from the Department and Semester column with our entered data. Then after collecting them all SUMPRODUCT returns the sum.

*[ Note: This is the alternative of method 2] *

**Step 2:** Enter the **Department **name and **Semester **and press Enter to see the result

**4. Using MAX IF formula with OR logic**

Now let’s try if we can find the maximum number or value conditionally with OR logic. Or logic means from two or multiple criteria at least one has to be met. We will use the same example that was used for the previous method but here our concern will be to find the maximum CGPA for two specific semesters.

**Step 1: **Enter the formula in cell **J4 **and press **Ctrl+Shift+Enter**

`=MAX(IF((E4:E16=J3) + (E4:E16=L3), F4:F16))`

**Formula Explanation**

We have used multiple conditions here and added them with a + sign. **(E4:E16=J3) + (E4:E16=L3) **This portion does the or operation. After that **IF((E4:E16=J3) + (E4:E16=L3), F4:F16) **finds the matched cells and finally, the **MAX **function returns the maximum value from the matched cells.

**Step 2: **Enter any two **Semesters **and see the result

**5. Find Maximum Value with Condition Using MAXIFS Function**

There is a function in Excel called **MAXIFS**. This function returns the maximum value from any dataset conditionally.

`MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`

**Max_range -> **This is required.The actual range of cells in which the maximum will be determined.

**Criteria_range1 -> **This is also required. Is the set of cells to estimate with the rules.

**Criteria1 -> **This is another required argument. Is the criteria in the form of a number, expression, or text that defines which cells will be evaluated as maximum. The same set of criteria works for the **MINIFS**, **SUMIFS**, and **AVERAGEIFS **functions.

**[criteria_range2, criteria2], … -> **This portion is optional. We can pass Additional ranges and their associated criteria. You can enter up to 126 range/criteria pairs.

For more information, you can visit this Link

Now let’s see how we can use the **MAXIFS **function for the previous examples.

Here our concern will be to find the maximum Fees for any specific **Department **and **Semester**.

**Step 1: **Enter the formula in cell **J5**

`=MAXIFS(G4:G16, D4:D16, J3, E4:E16, J4)`

**Formula Explanation**

Firstly we have passed the range where we will find the maximum value **G4:G16**, then the first criteria range which is **D4:D16**, then the input value cell number for the first criteria **J3**. Similarly, **E4:E16, J4 **does the same thing for the second criteria.

**Step 2: **Enter and Department name and Semester. Then press **Enter**

**User Problems**

### 1. How to find absolute max value in Excel

Sometimes we may need to find the maximum values from and positive or negative values. Let’s see the formula for this:

`=MAX(ABS(B4:B10))`

Enter the formula the press **Ctrl+Shift+Enter **(make it as array formula)

**2. How to Find the Maximum Value in One Column but Only if the Number is an Integer**

Let’s say we have data with decimal values but we do not want to consider the number after the decimal places. Then we can try this formula:

`=MAX(INT(B4:B10))`

Enter the formula the press **Ctrl+Shift+Enter **(make it as array formula)

### 3. **How to find Maximum Values Ignoring Zeros**

In our dataset, we may have some values as 0. In terms of finding maximum values, we can ignore them by entering the formula.

`=MAX(IF(B4:B10<>0, B4:B10))`

Enter this formula and press **Ctrl+Shift+Enter **(make it as array formula)

Or you can use

**=MAXIFS(B4:B10,B4:B10,”<>0″)**

(Without array formula)

**Conclusion**

These are the ways to find maximum value in Excel with conditions. I have shown all the methods with their respective examples but there can be many other iterations. Also, I have discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.