How to use MINIFS function in Microsoft Excel

Today we will continue exploring MIN function and find out some more ways of finding the smallest number based on one or multiple conditions in Excel. I'll show you the combination of MIN and IF 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 formulas with one another in many ways to solve as many different tasks as you may only think of. In this article, I'd like to continue making our acquaintance with MIN, show you some more ways of using it and offer an elegant alternative.

Shall we begin?

MIN with several conditions

A while back I showed you the usage of MIN and IF functions so that you could find the smallest number on the basis of 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 requirements? What should you do then?

When you know how to discover a minimum with 1 limitation using MIN and IF, you may wonder about the ways to detect it by two or even more parameters. 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 your solution:

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

Alternatively, you may avoid multiple IFs 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 alternative way to get the smallest number of apples sold in the south would be the following:

{=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 is an array formula which should be entered by Ctrl + Shift + Enter.

MINIFS or how to easily find the smallest number based on one or several conditions

MINIFS returns the minimum value by one or multiple guidelines 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.

Explore the syntax of MINIFS

This formula goes through your data range and returns you the smallest number according to the parameters you set. Its syntax 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 requirement
  • Criteria1 (required) - the condition to check Range1 for
  • [range2], [criteria2], … (optional) - additional data range(s) and their corresponding requirements. You are free to add up to 126 criteria and ranges in one formula.

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

Let's get back to our examples and check how easy the solution may be.

Use MINIFS to get a minimum by one criterion

The charm of MINIFS is in its simplicity. Look, you show it 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 to solve 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 parameter in and the parameter itself.

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

Find a minimum on the basis of multiple conditions with MINIFS

I showed you the way to locate the lowest number determined by 1 requirement using MINIFS. It was pretty easy, right? And I believe by the time you finish reading this sentence, you'll realize that you already know how to locate the smallest number by several criteria :)

Here is an update for this task:

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

MINIFS formula with 2 conditions

Note! The size of min_range and all the criteria_range must be the same so that the formula works correctly. Otherwise, you'll get the #VALUE! error instead of the correct result.

How to find the smallest number without zeroes using MINIFS

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

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

Minimum without zero using MINIFS

Using MINIFS to locate the smallest value by a partial match

When locating the bottom number, it may turn out that your search is not entirely accurate. There may be some extra words, symbols or accidental spaces after the keyword in your data range that may prevent you from getting the expected result.

Luckily, wildcards may be used in MINIFS 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 figure of all, just put an asterisk right after the search word so that the formula looks like this:

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

Find the smallest by a partial match using MINIFS

In this case, it 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 MINIFS) 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 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