How to use MINIFS function in Microsoft Excel

Today we will continue exploring Excel MIN function and find out some more ways of finding the smallest number based on one or multiple conditions. I'll show you the combination of MIN and IF functions and then tell you about the brand-new MINIFS function to prove that this one is definitely worth your attention.

I've already narrated about the MIN function and its capabilities. But if you have been using Excel for some time, I believe you know that you can combine functions with one another in many ways to solve as many different tasks as you can only think of. In this article, I'd like to continue making our acquaintance with the MIN function, show you some more ways of using it and offer an elegant alternative.

Shall we begin?

Excel MIN function with several conditions

A while back I showed you the usage of MIN and IF functions so that you could find the smallest number based on some criterion. But what if one condition is not enough? What if you need to conduct a more complex search and locate the lowest value based on a few conditions?

When you know how to find a minimum with 1 condition using MIN and IF, you may wonder about the ways to find it by two or more criteria. How can you do that? The solution would be as obvious as you think – using MIN and 2 or more IF functions.

So, in case you need to find the lowest quantity of apples sold in a particular region, here is a formula for you:

{=MIN(IF(A2:A15=F2,IF(C2:C15=F3,D2:D15)))}

Alternatively, you can avoid multiple IFs in one formula by using the multiplication symbol (*). Since you apply an array formula, the AND operator is replaced with an asterisk. You may check this page to refresh your knowledge about logical operators in array functions.

Thus, the second formula for getting the smallest number of apples sold in the south would be:

{=MIN(IF((A2:A15=F2)*(C2:C15=F3),D2:D15))}

Use MIN and IF functions to find smallest by 2 criteria

Note! Remember that the combination of MIN and IF functions is an array formula which should be entered by Ctrl + Shift + Enter.

Excel MINIFS function or how to easily find minimum based on one or several conditions

The MINIFS function returns the minimum value based on one or multiple criteria you specify. As you can see from its name, this is a combination of MIN and IF.

Note! This function is available only in Microsoft Excel 2019 and in the newest versions of Office 365.

The syntax of Excel MINIFS function

Excel MINIFS goes through your data range and looks for the smallest value based on the conditions you set.

The syntax for MINIFS is as below:

=MINIFS (min_range, range1, criteria1, [range2], [criteria2], …)
  • Min_range (required) - the range to find the minimum in
  • Range1 (required) - the set of data to check for the first criteria
  • Criteria1 (required) - the condition to check Range1 for
  • [range2], [criteria2], … (optional) - additional data range(s) and their corresponding criteria. You are free to add up to 126 criteria and ranges in one formula.

Remember us looking for minimum using MIN and IF and hitting Ctrl + Shift + Enter to turn it into the array formula? Well, for the Office 365 users there is another solution available. Spoiler alert – it's easier :)

Let's get back to our examples and check how MINIFS works.

Use MINIFS to get a minimum by a criterion

The charm of MINIFS is in its simplicity. Look, you show the formula the range with numbers, a set of cells to check for the condition and the condition itself. It's easier done than said actually :)

Here is the new formula for our previous case:

=MINIFS(B2:B15,A2:A15,D2)

Look for minimum using MINIFS

The logic is as simple as ABC:

A - First goes the range to check for the minimum.

B - Then the cells to look the condition in and the condition itself.

C - Repeat the last part as many times as there are conditions in your formula.

Find a minimum based on multiple conditions with MINIFS function

I showed you the way to locate the lowest number based on 1 condition using MINIFS. It was pretty easy, right? And I believe by the time you finish reading this sentence, you'll find out that you already know how to locate the minimum number by several criteria :)

Here is an updated formula for this case:

=MINIFS(D2:D15, A2:A15, F2, C2:C15, F3)

MINIFS formula with 2 conditions

Note! The size of min_range and all the criteria_ranges must be the same for the formula to work correctly. Otherwise, you'll get the #VALUE! error instead of the minimum.

How to find the smallest value without zeroes using MINIFS

The conditions you specify for the MINIFS function can be not only some words and values, but also expressions with logical operators (>,<,<>,=). I'm saying that you can locate the minimum value that is more than zero using just one formula:

=MINIFS(B2:B15, B2:B15, ">0")

Minimum without zero using MINIFS

Using MINIFS to locate minimum by a partial match

When looking for the bottom value, it may turn out that your search is not entirely accurate. There may be some extra words, symbols or accidental space after the keyword that may prevent you from getting the expected result.

Luckily, wildcards can be used in the MINIFS function and be your little savers in this situation. So, if you know for sure that there are multiple different entrances of, let's say, apples in your table and you need to find the smallest value of all, just put an asterisk right after the search word so that the formula look like this:

=MINIFS(C2:C15,A2:A15,"Apple*")

Find the smallest by a partial match using MINIFS

In this case, the formula will check all the occurrences of apple followed by any words and symbols and return you the smallest number from the Sold column. This trick may become a real time and nerve saver when it comes to partial matches.

They say "Old is gold". But as far as you can see something new (like the MINIFS Excel function) may be even better. It is simple, effective and there is no need to keep in mind the Ctrl + Shift + Enter combination all the time. Using MINIFS function in Excel you can easily find the smallest value based on one, two, three, etc. conditions.

But if you prefer the "old gold", the MIN and IF pair will do the trick for you. It will take a few more button clicks, but it works (isn't it the point?)

I hope you enjoyed your reading today. In case you have any questions or other examples in mind, please leave your thoughts in the comments section.

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard