Excel is a powerful tool when it comes to analyzing large sets of data. With the introduction of the MAXIFS and MINIFS functions in Excel, users now have the ability to easily and quickly find the maximum and minimum values of a specific range based on one or more conditions. In this article, we’ll discuss how to use the MAXIFS and MINIFS functions in Excel.

What are MAXIFS and MINIFS Functions?

MAXIFS and MINIFS are two of the newest functions in Excel that allow users to find the highest or lowest value for a given range based on certain conditions. These functions are part of the “IFS” family of functions, which means they are designed to perform calculations based on one or more specified criteria.

As the MAXIFS name suggests, the function can find the maximum value among a range of cells that meets the specified conditions. Similarly, the MINIFS function finds the lowest value in a range based on the provided criteria.

Syntax of MAXIFS and MINIFS functions

The syntax of the MAXIFS and MINIFS functions is similar, with the only major difference being that MAXIFS function will return the maximum value from a range of cells whereas MINIFS function will return the minimum value from a range of cells.

MAXIFS:

=MAXIFS (range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

MINIFS:

=MINIFS (range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Where:

• Range: This is the range in which you want to find the maximum or minimum values based on the conditions you will specify.

• Criteria_range1: The first criteria range which will determine the condition for which the maximum or minimum value will be selected.

• Criteria1: The criteria for the first range to be selected.

• Criteria_range2, Criteria2: Additional criteria ranges that will refine your search.

How to Use MAXIFS and MINIFS Functions?

To illustrate the use of these functions, we can take a typical sales dataset that contains various products and their corresponding sales information across different regions of the world.

Suppose you want to find the maximum sales value for “Product A” in the “North America” region. To do this, we can use the MAXIFS function as shown in the following example:

=MAXIFS (Sales, Products, “Product A”, Region, “North America”)

The MAXIFS function takes the range of sales data as its first argument, followed by the criteria_range1 as “Products”, and criteria1 as “Product A”. The next criteria_range2 is set as “Region” and criteria2 as “North America”.

Similarly, to find the minimum sales value for “Product B” in the “Asia” region, we can use the MINIFS function.

=MINIFS (Sales, Products, “Product B”, Region, “Asia”)

Here, the MINIFS function takes the range of sales as its first argument, followed by the criteria_range1 as “Products”, and criteria1 as “Product B”. The next criteria_range2 is set as “Region” and criteria2 as “Asia”.

Conclusion

In summary, the MAXIFS and MINIFS functions in Excel are valuable tools for anyone who needs to find the maximum or minimum value within a specified range based on certain conditions. By using these functions, you can save valuable time and improve the accuracy of your analysis. Remember to always carefully consider the criteria you are using when applying these functions to ensure that you get the desired results.