How to Use MAXIFS and MINIFS in Excel: Step-by-Step (2024)

If you think the word MAXIFS and MINIFS come from maximum and minimum – you are absolutely on point 🎯

MAXIFS is the combination of the MAX and the IFS function. And likewise, MINIFS is the combination of MIN and IFS functions.

What do these functions do? How do they do it? All of this and much more await you in the guide below.

So let’s dive right in. And oh yeah, here’s the free sample workbook here for you to download and tag along with the guide 📩

How to use MAXIFS

The MAXIFS function returns the largest (maximum) numeric value from a given range that meets a specified criterion.

How? Let’s see that here.

The image above has a list of employees. In the corresponding cells, we have their salaries and respective departments.

Now let’s say from this list – we want to find the highest salary being drawn from the Tax department 📝

The MAXIFS function will help you do that.

1. Begin writing the MAXIFS function as follows:

= MAXIFS (

2. The max_range refers to the range from where the maximum value is sought.

We want to find the maximum salary so, our max_range is the column for salary. Here we’re creating a reference to the range C2:C8.

= MAXIFS (C2:C8,

3. Define the range for the first criterion.

= MAXIFS (C2:C8, B2:B8

Criteria range 1 is the first range that evaluates the first criteria.

We want the maximum salary, but from the Tax department only. So our criteria range is the column for departments i.e. B2 to B8.

4. Specify the criteria to be looked up in the criteria range.

We are looking for the maximum salary from the department of “Tax”. And that’s our criterion 📌

= MAXIFS (C2:C8, B2:B8, “Tax”)

5. Hit Enter. And there you go!

The MAXIFS function fetches the maximum salary from the Tax department only. And that is $9000 💵

As simple as it could be!

How to use MINIFS

The MINIFS function is the polar opposite of the MAXIFS function. It returns the smallest (minimum) numeric value from a given range that meets a specified criterion.

How? Let’s redo our previous example. But this time we are on the hunt for the minimum salary drawn from the tax department🏹

1. Begin writing the MINIFS function as follows:

= MINIFS (

2. As the min_range, refer to the range from where the minimum value is sought.

We want to find the minimum salary, so our min_range is the column for salary.

And so, we’re creating a reference to the range C2:C8.

= MINIFS (C2:C8,

3. Define the range for the first criterion.

= MINIFS (C2:C8, B2:B8

As we want the minimum salary from the Tax department only, our criteria range is the column for departments i.e. B2 to B8 🎯

4. Specify the criteria to be looked up in the criteria range.

We are looking for the minimum salary from the department of “Tax”. And that’s our criterion.

= MINIFS (C2:C8, B2:B8, “Tax”)

5. Hit Enter.

The MINIFS function fetches the minimum salary from the Tax department only.

And that is $6000 💲

MAXIFS and MINIFS formula examples

The examples for MAXIFS and MINIFS are not coming to an end anytime soon 🔚 We yet have a long way to go.

There’s so much that you can do with these functions – let’s explore all these options below.

MAXIFS with logical operators

Who said the MAXIFS function only worked with text criteria? You can also use it to evaluate numeric criteria. And that is where you need logical operators.

For example, from the same example as above, let’s now find out the salary that is greater than $9500. For that:

1. As the first argument (max_range), specify the range from where the maximum value is sought (salaries):

= MAXIFS (C2:C8,

2. Define the range for the first criterion. Our first criterion is “greater than $9500”, and so, our criteria range is also Salaries (i.e. C2:C8).

= MAXIFS (C2:C8,C2:C8,

3. Specify the criteria to be looked up in the criteria range.

Our criterion is “greater than $9500”. Using the “greater than” logical operator, this would become “>9500” ✌

= MAXIFS (C2:C8, C2:C8, “>9500”)

Pro Tip!

To type in a numeric criterion as text inside your function, enclose it in quotation marks. Like “>9500” in the example above.

Or you can create a reference to a cell (say cell B10) that contains the value 9500. In that case, the criteria would be specified as “>”&B10. In short, enclose the logical operator in quotation marks, an ampersand, and then the cell reference.

4. Hit Enter.

The MAXIFS function finds the salary greater than $9500. And we have it as $10,000!

Max value within a range of dates

Do you know? Excel stores dates as serial numbers where serial number 01 is equal to 01 January 1900. 02 January 1900 stands equal to serial number 2 and so on.

Considering dates as serial numbers, you can easily apply the MAXIFS function to find out the earliest or farthest date. Let’s see an example of this.

Here we have some sale transactions that occurred on different dates 📅

Let’s use the MAXIFS function to find the date when the latest sales for Product A occurred (the maximum date).

1. Begin writing with the max_range for the MAXIFS function.

We want to find the farthest date, so that makes our max_range i.e. A2:A7.

= MAXIFS (A2:A7

2. Define the range for the first criterion. Our first criterion is sales of Product “A”.

So we are creating a reference to the column for products (i.e. B2:B7).

= MAXIFS (C2:C7, B2:B7

3. Specify the criteria to be looked up in the first criteria range.

Our first criterion is sales of Product “A”, and we will write the same in the function below 🅰

= MAXIFS (C2:C7, B2:B7, “A”)

4. Hit Enter.

The MAXIFS function finds the maximum (farthest) date when the sales for Product A occurred. And that is 30 Sep 2022!

Interesting, no?

The minimum value within a range of dates

You can also use the MINIFS function with a range of dates. Want to see how?

Check this out 👀

Using the same example as above, let’s use the MINIFS function to find the minimum sales for Product A on 25 March 2022.

1. Begin writing with the min_range for the MINIFS function. We want to find the minimum sales so that makes our min_range i.e. Cell C2:C7.

= MINIFS (C2:C7

2. Define the range for the first criterion. Our first criterion is sales of Product “A”.

So we are creating a reference to the column for products (i.e. B2:B7).

= MINIFS (C2:C7, B2:B7

3. Specify the criteria to be looked up in the first criteria range 🔍

Our first criterion is sales of Product “A” and we will write the same in the function below.

= MINIFS (C2:C7, B2:B7, “A”

4. Our second criterion is 25 March 2022. Refer to the column of dates (A2:A7) as the second criterion.

= MINIFS (C2:C7, B2:B7, “A”, A2:A7

5. Define the second criterion as 25-Mar-2022.

= MINIFS (C2:C7, B2:B7, “A”, A2:A7, “25-Mar-2022”)

Pro Tip!

If you specify the date in the form of a text string, make sure it’s in a format understandable by Excel. An easy format could be mm/dd/yyyy 📝

Or you can enclose the date in the DATE function like DATE(2022,03,25).

6. Hit Enter to see the results.

We have a total of two sales transactions for Product A on 25 March 2022. One is for $2000 (Row 2), and the other is for $500 (Row 5).

Excel fetches the minimum sales from these i.e. $500 🚀 Cool enough, no?

MAXIFS with OR logic instead of AND logic

Both the MAXIFS and the MINIFS functions operate under the AND logic. What does that mean?

This means that if you supply two conditions to these functions, they will search for a value that meets both those conditions.

However, sometimes you might want to operate the MAXIFS function with the OR logic i.e. return the value that meets any one of the two (or more) supplied conditions.

For example, take a look below:

Same old example. But this time, we want to know the highest salary from the Tax or the Sales Department.

Now, applying the simple MAXIFS function will never yield a result for it 🥴

For example, you might be thinking to apply the MAXIFS function with two criteria. One for the highest salary from the tax department, and the second for the highest salary from the sales department.

If you do so, the formula would look like this:

= MAXIFS (C2:C8, B2:B8, “Tax”, B2:B8, “Sales”)

And the results would be like this:

There doesn’t exist any salary that belongs to both the Tax and the Sales departments, both. So you get a zero

Here, we need to run the OR logic. Maximum salary from the Tax OR the Sales department. To do that:

1. Write the MAXIFS function for fetching the highest salary from the Tax department as follows:

= MAXIFS (C2:C8, B2:B8, “Tax”)

Individually, this function will get fetch the maximum salary from the Tax department 🚩

2. Write the MAXIFS function for fetching the highest salary from the Sales department as follows:

= MAXIFS (C2:C8, B2:B8, “Sales”)

Individually, this function will also find the maximum salary from the Sales department.

We have both formulas for getting the highest salary from each department. It’s time that we put them together.

But to find the highest salary from these two numbers:

• Highest salary from the Tax department
• Highest salary from the Sales department

We need to apply the MAX function once again 💪

3. Nest the above two functions as the two arguments of the MAX function as follows:

= MAX (MAXIFS (C2:C8, B2:B8, “Tax”), MAXIFS (C2:C8, B2:B8, “Sales”))

The MAX function will choose the maximum value from these two values. And we will get the highest salary from the Tax or the Sales department.

4. Go ahead and press Enter.

Here come the results 😎

The MAXIFS and the MINIFS function cannot be directly used to run an AND operation. However, tweaking the formulas a little can help you reach the goal.

That’s it  – Now what?

The guide above teaches us everything about the MAXIFS and MINIFS function. Starting from the basic application of these functions in Excel to various examples of each of them using logical operators, dates, and whatnot.

Both of these functions are interesting and much useful in sorting data. And so are the other functions of Excel 👩‍🏫

To your good, Excel has a huge variety of functions to offer to its users. Some key Excel functions include the VLOOKUP function, IF function, and SUMIF function.

Here is the link to my 30-minute free email course that covers all details about these (and many more) Excel functions. Sign up now.

Other resources

If you liked reading about the MAXIFS and MINIFS function, you’d also want to know about the MAX and the MIN functions.

In older versions of Excel, you can use the MAX function together with the IF function in place of the MAXIF function.

Frequently asked questions