How to use MINIFS function in Microsoft Excel

The MINIFS function in Excel is a powerful tool for filtering and analyzing data. It allows you to find the smallest numeric value in a range of cells that meet one or more specified requirements. This can be useful for a variety of tasks, such as finding the lowest price for a product in a specific category, finding the shortest delivery time for a product, finding the highest test score for a student in a particular class, or finding the lowest employee salary in a department.

What is the MINIFS Function in Excel

The MINIFS formula in Excel is a combination of the MIN and IF functions. To use it, we provide a range of values to evaluate for the minimum, along with one or more sets of criteria. Each set of criteria consists of a range and a condition, then the MINIFS would return the minimum value that meets all the specified criteria.

To define the criteria, MINIFS supports logical operators like (>,<,<>,=) and wildcards like asterisks(*) and question marks (?) to match the partial text or patterns.

Note: Note: This feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription.

MINIFS Function Formula

The below formula will return the smallest value from a range based on the criteria you set.

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

Here’s what each argument represents:

• min_range: This is the range of values you want to find the minimum from.

• criteria_range1: The first range where you want to apply a condition.

• criteria1: The condition to apply to criteria_range1.

• [criteria_range2, criteria2, …]: Optional additional ranges and criteria. You can have multiple pairs of criteria ranges and criteria.

How to use the MINIFS Function in Excel

Step 1: Organize Your Data in Excel

Start by putting your information into an Excel spreadsheet. This data can be anything you want to work with.

Step 2: Use the MINIFS Function

In Excel, you’ll write the MINIFS function. This function lets you find the smallest value in a set of data that meets specific conditions. You’ll specify which cells to look at and what conditions those cells should meet.

Step 3: Choose the Data to Analyze

Finally, you’ll select the particular data you want to analyze using the MINIFS function. This tells Excel where to find the information you’re interested in, and the function will then find the smallest value that fits your criteria from that data.

MINIFS Function in Excel Examples

1. How to Use MINIFS to get a Minimum by One Criteria
2. How to Use Use MINIFS to get minimum value based on multiple criteria
3. How to Use the MIN Function With Multiple IF Functions
4. How to easily find the smallest number based on one or several conditions
5. How to find the smallest number without zeroes using MINIFS
6. How to Use MINIFS to locate the smallest value by a partial match

How to use MINIFS to get a Minimum by One Criteria

MINIFS function can be used to find the minimum value based on a single criterion. If you want to use the MINIFS function to find the minimum value based on a single criterion, you just need to specify a single criterion. MINIFS works fine with single or multiple criteria.

Here’s the syntax for using MINIFS with a single criterion:

=MINIFS(min_range, criteria_range1, criteria1)

Below is an example dataset that contains products quantity sold in different city monthly. Suppose we need to find the minimum value of “Green Tea” sold.

The formula for this condition can be written in three different ways:

1. =MINIFS(D2:D14,A2:A14,”Green Tea”)

1st way of using MINIFS with one criteria

• D2:D14 is the min_range, where we want to find the minimum quantity sold.

• A2:A14 is the first criteria range, which checks the “Product” column for “Green Tea.”

• “Green Tea” is the first criteria, specifying the Green Tea Product.

The MINIFS function will return the minimum quantity sold that meets the criteria, which in this example would be 349.

2. =MINIFS(D2:D14,A2:A14,F4)

2nd way of using MINIFS with one criteria

• D2:D14 is the min_range, where we want to find the minimum quantity sold.
• A2:A14 is the first criteria range, which checks the “Product” column for “Green Tea.”

• F4 is the first criteria, specifying the Green Tea Product.

The MINIFS function will return the minimum quantity sold that meets the criteria, which in this example would be 349.

3. =MINIFS(D2:D14,A2:A14,A2)

MINIFS with one criteria

• D2:D14 is the min_range, where we want to find the minimum quantity sold.

• A2:A14 is the first criteria range, which checks the “Product” column for “Green Tea.”

• A2 is the first criteria, specifying the Green Tea Product.

The MINIFS function will return the minimum quantity sold that meets the criteria, which in this example would be 349.

How to use MINIFS to get minimum value based on multiple criteria

The above example depicted the use of MINIFS when there is only one criterion available. What if we need to fetch minimum value from a range based on multiple criteria. You can use it when you have a dataset with multiple conditions that need to be met before calculating the minimum value.

Here’s the syntax of using MINIFS with multiple criteria:

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

• min_range: This is the range of values you want to find the minimum from.

• criteria_range1: The first range where you want to apply a condition.

• criteria1: The condition to apply to criteria_range1.

• [criteria_range2, criteria2, …]: Optional additional ranges and criteria. You can have multiple pairs of criteria ranges and criteria.

Lets’s say we need to fetch the minimum value of quantity sold of “Black Coffee” in “Chennai” city. The formula for this condition can be written as :

=MINIFS(D2:D14,A2:A14,F4,C2:C14,G4)

• D2:D14 is the min_range, where we want to find the minimum quantity sold.

• A2:A14 is the first criteria range, which checks the “Product” column for “Black Coffee”.

• F4 is the first criteria i.e., Black Coffee.

• C2:C14 is the second criteria range, which checks the “City” column for “Chennai”.

• G4 is the second criteria i.e., Chennai.

The MINIFS function will return the minimum quantity sold that meets both criteria, which in this example would be 450.

How to use the MIN Function With Multiple IF Functions

If you’re using an older version of Excel that doesn’t support MINIFS, you can use an array formula with MIN and IF functions to achieve the same result.

MIN function in combination with multiple IF functions to find the minimum value based on specific conditions. This approach can be particularly useful when you have complex criteria that you want to apply to your data. If we want to get the smallest value from a range based on singe or multiple conditions, in that case we can make use of MIN with IF function. There can be multiple number of IF based on how many conditions you want to apply.

MIN with IF is an array function, so to execute the cell we should run the formula cell with CTRL + SHIFT + ENTER. You can add as many conditions as needed to meet your specific criteria.

Here’s a general formula using the MIN function with multiple IF functions:

=MIN(IF(condition1, range), IF(condition2, range), IF(condition3, range), …)

• condition1, condition2, condition3…, : These are the conditions you want to apply to your data. Each condition should return either TRUE or FALSE.

• range: This is the range of values you want to evaluate. The IF functions will return values from this range if the corresponding condition is TRUE.

Suppose we need to find the minimum number of “Cupcakes” sold in “Banglore” city.

Here, our first criteria range will be A2:A14 and the value we want from this range is F4(Cupcakes), next criteria range is C2:C14 and the value we want from this range is G4(Banglore). And the final output we need after checking both the criteria if the minimum quantity sold from range D2:D14.

Note: Remember to enter this formula as an array formula by pressing Ctrl+Shift+Enter to ensure it works correctly.

MIN with IF Formula

Alternatively, if you want to write the above formula skipping multiple IF conditions, you can make use of (*) symbol. It will give the same result.

MIN IF with (*) Symbol

How to Easily find the Smallest Number Based on One or Several Conditions

To easily find the smallest number based on one or several conditions you can make use of MINIFS if you are using latest version is Excel 2019.

Syntax:

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

In the same way if you are using older version of Excel prior to Excel 2019, you can use an array formula with MIN and IF function

Syntax:

=MIN(IF(condition1, range), IF(condition2, range), IF(condition3, range), …)

The use of both the methods for finding smallest value based on multiple conditions have been explained in the above section.

How to find the smallest number without zero using MINIFS in Excel

To find the smallest number in a range without including zeroes using the MINIFS function in Excel, you can set up a condition that excludes zeros from the criteria. You can use (>,<,=,<>) in the criteria.

Let’s make a change in our dataset to understand the use of MINIFS without zeroes. Change the quantity of Cakes sold in March to 0.

Now, suppose we need to find the minimum value of quantity sold other than zero. The formula can be written as:

=MINIFS(D2:D14,D2:D14,”>0″)

MINIFS without zeroes

• D2:D14 is the min_range, where we want to find the minimum quantity sold.

• D2:D14 is the first criteria range, which checks the “Qty Sold” column for value greater than 0.

• “>0” is the first criteria.

The MINIFS function will return the minimum quantity sold other than 0 which in this example would be 233.

You can use the same formula by adding multiple conditions to find the minimum Qty Sold of “Cakes” other than 0. The formula will be:

=MINIFS(D2:D14,D2:D14,”>0″,A2:A14,”Cakes”)

• D2:D14 is the min_range, where we want to find the minimum quantity sold.

• D2:D14 is the first criteria range, which checks the “Qty Sold” column for quantity sold other than 0.

• “>0” is the first criteria.

• A2:A14 is the second criteria range, which checks the “Product” column for “Cakes”.

• “Cakes” is the second criteria.

The MINIFS function will return the minimum quantity sold that meets both criteria, which in this example would be 250.

How to Use MINIFS to locate the smallest value by a partial match

In the dataset you might come across some data that is not accurate. This data can prevent you from getting the desired result. The inaccuracy in the data can be containing extra words, symbols or spaces. Here comes the use of wildcards along with MINIFS which eliminates these extra words, symbols, spaces and provides the desired output.

Suppose, from our dataset you need to find the minimum quantity sold of Product “Cakes”. The formula can be written as:

=MINIFS(D2:D16,A2:A16,”Cakes”)

MINIFS without Wildcards

The above formula will only compare two values of “Cakes”, 981 and 155. Out of which 155 is the minimum value.

We can modify the same formula by appending Cakes with an asterisks to find the smallest figure of all. The formula will look like:

=MINIFS(D2:D16,A2:A16,”Cakes*”)

MINIFS with wildcards

In this case, it will check all the occurrences of Cakes followed by any words and symbols and return you the smallest number from the Qty Sold column.

Conclusion

Using Excel functions like MINIFS can significantly enhance your efficiency in various fields, such as education and data analysis. MINIFS allows you to find the smallest value in a dataset based on specific conditions. By following the provided steps, you can effectively utilize this function in your work.

FAQs

How do I use Maxifs and Minifs in Excel?

MAXIFS Function:

Select a cell: Click on the cell where you want the maximum value to appear.

Type the formula: Enter =MAXIFS(range, criteria_range, criteria) where:

range: The range of values you want to find the maximum from.

criteria_range: The range where you apply a condition.

criteria: The condition to apply to the criteria range.

Press Enter: Excel will calculate and show the maximum value based on your criteria.

MINIFS Function:

Select a cell: Click on the cell where you want the minimum value to appear.

Type the formula: Enter =MINIFS(range, criteria_range, criteria) where:

range: The range of values you want to find the minimum from.

criteria_range: The range where you apply a condition.

criteria: The condition to apply to the criteria range.

Press Enter: Excel will calculate and display the minimum value based on your criteria.

These functions help you find maximum and minimum values while considering specific conditions. Customize the criteria_range and criteria to suit your needs.

Can we use MINIFS with dates and other data types?

Yes, MINIFS can be used with data types like dates, number and text.

Is MINIFS available in older version of Excel?

No, MINIF is not available in excel version older to excel 2019. Alternatively you can use the combination of MIN with IF function.

Can MINIFS handle more than one criteria range?

Yes, MINIFS can handle multiple criteria ranges and criteria pairs. You can add as many criteria ranges and criteria as needed.

How do I handle errors or empty cells when using MINIFS with multiple criteria?

You can use IFERROR with MINIFS to avoid the error. =IFERROR(MINIFS(min_range, criteria_ange1, criteria1, criteria_range2, criteria2), “No Match”).

Is there an alternative to MINIFS for finding the minimum value based on multiple criteria in older version of excel?

For excel version prior to Excel 2019 you can make use of combined MIN and IF to get the desired result.