< Go Back

Maximum if multiple criteria

Maximum if multiple criteria

In order to get the maximum value of a data set that is based on one or more criteria, there can be a wide variety of formulas based on MAX and IF function. It is further explained in the example figure below:

Figure 1: MAX and IF function to return the maximum value

Generic Syntax

As stated above, there can be a variety of formulas that can be derived from the MAX and IF function. One of the formula made by these functions in the example figure is:

=MAX(IF(color=G7,IF(item=H7,price)))

With the color of blue and the item hat, the result is the price $11.00.

Working of the formula

This example uses the named angels of “color” = B7:B14, “item” = C7:C14 and “price” = E7:E14 respectively. The target here is to find out the maximum price for the given color and item.

This formula uses two IF function that is enclosed inside a MAX function in order to return the minimum price within the criteria. The first IF function is based on color “red” and the second IF function is based on price. Hence the color and price have been specified which in this case were blue and hat respectively.

Alternate formulas using MAX and IF function

There is an alternate way to use the MAX and IF function that forms the following formula:

=MAX (IF ((color=G7) * (item = H7), price))

Here the color and item criteria have been separately specified and the ending part has been specified for the price display.

Another alternate formula can be:

=MAXIFS (price, colour, G7, item, H7)

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar