How to Find Maximum Value in Excel with Condition (5 Ways)

Enter any number and press enter

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.

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.

Find Maximum Value with Condition Using MAX Function

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.

Enter the formula in cell K4

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

Enter any number 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.

 Find maximum value using MAX IF formula

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.

Enter the formula using MAX IF

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

enter any department 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.

 Using MAX IF without array

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.

 Enter formula without array using MAX IF

[ Note: This is the alternative of method 2] 

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

Enter department and semester

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.

 Using MAX IF formula with OR logic

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.

Enter the formula and press ctrl+shift+enter

Step 2: Enter any two Semesters and see the result

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.

Find Maximum Value with Condition Using MAXIFS Function

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.

Enter the formula in cell J5

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

Enter department and semester

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)

How to find absolute max value in excel

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)

find maximum from float values

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)

How to find maximum values ignoring zeros

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.


Further reading:

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo