Filter and calculate data with MAXIFS and MINIFS

If you’re familiar with COUNTIFS, SUMIFS and AVERAGEIFS, then MAXIFS and MINIFS don’t need much explanation. The classic MAX and MIN functions calculate the maximum or minimum value in a range, but what if you need to apply conditions to filter your data? This is precisely what MAXIFS and MINIFS allow. You can specify one or more conditions that filter the data before calculating the max or min. The conditions can be applied to adjacent ranges or the range that contains the values. For example, let’s say a retailer has a table containing sales data for all their stores. They can use the MAXIFS and MINIFS functions to calculate the maximum and minimum sales figure for a specified item in stores located in a specified region.

In the example shown below, MINIFS and MAXIFS are used to calculate the min and max sales figures from the table, but it only includes values from the Sales column if the value in the Retailer column is “BigMart,” the value in the Brand column is “Longlast” and the value in the Sales column is greater than zero.


If you're familiar with COUNTIFS(), SUMIFS(), and AVERAGEIFS(), you'll appreciate MAXIFS() and MINIFS(). MAXIFS() lets you determine which values are evaluated by a traditional MAX() calculation.

This function uses the following syntax:

MAXIFS(range, criteriarange1, criteria1, [criteriarange2, criteria2], ...)

where range is the range of values you're evaluating, criteriarangex represents the range criteria that values in range must meet to be evaluated, and criteriax is the actual criteria value. Figure E shows a matrix that returns the maximum value from column B for each person by region using MAXIFS().

Figure E

Figure E This matrix uses MAXIFS() to return conditional maximum values.

In this case, precise absolute and relational referencing is necessary:

=MAXIFS($B$2:$B$33,$C$2:$C$33,$H2,$D$2:$D$33,I$1)

When the value in column C equals the value in column H and the region value in column D equals the value in row 1, the value in column B is considered for evaluation.

6: MINIFS()

As you might expect, MINIFS() is similar to MAXIFS(). The functionality and syntax are the same, but MINIFS() returns the minimum value. Figure F shows a second matrix using the following function to return the minimum value from column B for each person by region:

=MINIFS($B$2:$B$33,$C$2:$C$33,$H2,$D$2:$D$33,I$1)

Figure F Figure F Use MINIFS() to return a conditional minimum value from a range.