Smallest and highest range values

One of the updates of the Office365 package, within Excel 2016, has brought functions for conditional aggregation of data that find the minimum or maximum values ​​of the range depending on the given conditions. These are the functions MINIFS and MAXIFS, and in the following text I will show you how to use such functions.

As an example we will use a table containing the list of wines produced by a winery, with types and retail prices given in columns. How to find the cheapest or most expensive wine depending on the wine type?

To find the conditional maximum and minimum, the functions MAXIFS and MINIFS are used, the syntax of which is:

MAXIFS (<maximum search range>, <string1>, <criterion1> … <string n>, <criterion n>)

MINIFS (<minimum search range>, <string1>, <criterion1> … <string n>, <criterion n>)

The first argument of these functions is the range of cells, usually a column, within which we seek the maximum or minimum value. Further we add pairs of condition ranges and values. We have to input at least one pair of condition range and value.

For example, if we want to determine which white wines are the most expensive should write the formula:

= MAXIFS (D2: D11, C2: C11, “WHITE WINE”)

As a result, we get the value of the most expensive white wine. If we have used MINFS function we would get the most inexpensive.