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
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:
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)