How to find max value in range with criteria in Excel

Max, as used in the above statement, is the short form for the word maximum. Maximum is a mathematical function used when we are referring to the value with the largest limit in any data range. When data is defined by either minimum or maximum function, you know absolutely comparison is being done or differentiation of the data is being done.

In excel sheets, numeric values that are numbers, integers, and digits are recorded or keyed in. Because of this, getting the maximum value or the minimum value is vital. To find the maximum value in a given range with criteria may mean to find the largest value or figure in a couple of items or objects bearing the same name or identity but have different numeric values.

There are a couple of ways to reach this maximum result and which are discussed in length using a couple of steps which include the following;

Step 1

Come up with a certain set of data that will have different numeric values but the same object or item name.

Do this on a blank excel sheet on your computer or laptop. For example, you can use one column for the product and the other column for the order. Take a look at the sample in the image below.

Step 2

In the above excel sheet, we are going to draw the maximum value from the range with criteria. With the help of the =MAX function, we may be able to get the correct result. From the data set, we have the column for the result and another for the product. On the empty cell for the product, we write the product we need to find the max value and the result will appear on the other cell of the result value.

The whole formula will look like this =MAX ((A2: A8= D2)* B2: B8)). The first segment means we are taking all cells in column A and equating them to the cells in column B.


A representation of what the formula will do is shown in the screenshot above.