This blog is part 4 of a series that explores the amazingly useful functionality of some of the most popular Office 365 Excel functions. In the previous blog, the focus was on the UNIQUE function. Building on this, we will explore how useful the MAXIFS and MINIFS functions are in finding the highest or lowest value more efficiently.

What are the MAXIFS and MINIFS functions and why are they useful

The MAXIFS and MINIFS functions allow you to find the highest or lowest value based on one or more criteria. For example, an HR officer may need to find the highest or lowest salary from a specific department, e.g. Sales, from the dataset below.

The standard method for getting this result would be to filter the list on the specific criteria i.e. Sales and then select all the cells in the Salaries column and checking the status bar at the bottom of the screen for the highest or lowest salary. This does the job but is extremely manual and unproductive as similar methods would need to be employed for other departments. By using the MAXIFS and MINIFS functions instead, the results are all there in one place to do further analysis, e.g. using other formulas to create % bonuses for highest earners.

How to apply the MAXIFS and MINIFS functions

To calculate the Highest Salary per Department see below:

2. Copy down the MAXIFS formula for the other cells to create highest salaries for all other departments

3. Repeat this formula for the Lowest Salary per Department but replace MAXIFS with MINIFS:

Type =MINIFS($E$2:$E$29, $D$2:$D$29, H2)

In this HR example you may need to apply more criteria to limit the range even further to find the maximum value, e.g. the highest salary per department for a specific status such as status 2:

Conclusion

The MAXIFS and MINIFS functions are extremely useful in monitoring high and low performance without the need for filtering and so improves efficiency and productivity.