If you’re familiar with statistical functions like 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.
Now you can apply conditions and filter data before
calculating with MAXIFS and MINIFS in Excel.
The MAXIFS
and MINIFS functions are available if you are an Office 365 subscriber and have
the latest version of Office installed on your PC. It is also available in
Excel Online and in Excel Mobile or Excel for Android phones and tablets.
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.
The
MAXIFS function returns the maximum value among cells specified by a given set
of conditions or criteria.
Here is
the Syntax:
MAXIFS(max_range,
criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The
MINIFS function returns the minimum value among cells specified by a given set
of conditions or criteria.
Here is
the Syntax:
MINIFS(min_range,
criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The size
and shape of the max_range and criteria_range arguments must be the same,
otherwise these functions return the #VALUE! error.
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.
