How to use MINIFS Function in Excel

MINIFS Function in Excel offers a powerful tool for data analysis, allowing users to pinpoint the minimum value in a range based on specified criteria. With its flexibility and versatility, MINIFS enables precise filtering of data, making it indispensable for tasks requiring complex conditions. Whether you’re analyzing sales figures, tracking expenses, or evaluating performance metrics, MINIFS Function in Excel empowers you to extract valuable insights efficiently. By harnessing its capabilities, you can streamline your decision-making process and unlock the full potential of your data analysis endeavors.

This content covers:

1. What is MEANIFS mean?
2. Syntax & Arguments.
3. How to use the MINIFS Function in Excel?
4. Get the minimum value by one criterion using MINIFS.
5. Find the minimum value on the basis of multiple conditions with MINIFS.
6. Finding the minimum value through a partial match with MINIFS.
7. A few notes about the MINIFS Function.

1. What is c mean?

MINIFS is an Excel formula that can be used to identify the smallest value in a set of cells, determined by certain criteria. The MINIFS formula returns the minimum value that fulfills one or more conditions. When searching for a minimum value from varying cells that satisfy one or more specific criteria, Excel’s MINIFS function can assist in achieving this goal.

Unlike Excel’s MIN function, MINIFS allows you to specify specific subsets of data within the overall range.

An example will be the MINIFS function like this: Find the lowest score from a list of semester scores, where the score was obtained by a specific grade level and the test was taken in a specific semester.

2. Syntax & Arguements

The general MINIFS syntax  looks like this: =MINIFS(complete range of cells, first criterion range, first criterion, [sequential criterion ranges], sequential criteria])

• “=” is the symbol used to inform Excel that you want it to make a calculation. MINIFS is the calculation you want Excel to make.
• A complete range of cells is all cells with data from which you want to make a calculation.
• The first criterion range is the range where you apply your first criterion. Typically, this is the same as the complete range of cells.
• The first criterion is the first way you want to sort or filter data in the first criterion range.
• Sequential criterion ranges are any subsequent ranges on which you want to use the MINIFS function.
• Sequential criteria are any additional ways you want to sort of filter the data in their respective criterion ranges

Arguments

=MINIFS(min_range,range1,criteria1,range2,criteria2) // 2 conditions

• min_range: The range of cells where you will determine the smallest or minimum value.
• range1: The range of cells that you want to apply criteria1
• criteria1: The criteria used to determine which cells to evaluate as the smallest. criteria1is applied against range1.
• range2, … range_n: It is the range of cells that you want to apply criteria2, … criteria_nagainst. There can be up to 126 ranges.
• criteria2, … criteria_n: It is used to determine which cells to evaluate as the smallest. criteria2is applied against range2, criteria3 is applied against range3, and so on. There can be up to 126 criteria.

3. How to use the MINIFS Function in Excel?

 Step 1: First, you need to enter their data into an Excel spreadsheet to get an organized table of data as shown in the image.

Entered the information here.

Step 2: Add the column in and A10 and B10 to get the MINIFS result .

Added the column here.

Step 3: To find out the sales for quarter 3, Product A you need to use a formula.

The formula is: =MINIFS(D2:D8,A2:A8,”>3″,C2:C8,”A”)

Applied the formula here.

Step 4: The sales for Quarter 3, Product A is 0. This means There were no A product sold in Quarter 3.

The result is below.

4. Get the minimum value by one criterion using MINIFS

Here is a table and some data consisting of the Cricket team group, Player name, and their scores. You want to find the minimum value of Group A, for this follow the below steps:

Step 1: First, you need to enter the data into an Excel spreadsheet to get an organized table of data as shown in the image.

Placed the information into the table.

Step 2: Now you want to find out the lowest score off Group A, so add another column in E1 and E2 to get the Min for Group A result there.

Added another column here.

Step 3: To find out the minimum score of Group A among all players you need to use a formula.

The formula is: =MINIFS(C2:C4,A2:A4,”Group A”)

Applied the formula here.

Step 4: Press Enter, you will get the output.

The below image shows the result of the minimum value of group A is 69.

5. Find the minimum value on the basis of multiple conditions with MINIFS

Step 1: First, you need to enter their data into an Excel spreadsheet to get an organized table of data as shown in the image.

Placed the data here.

Step 2: Now, add another column in E1 and E6 to get the result of Min for Group A, Min for Group B and Min for the player starting with A results there.

Added another column here.

Step 3: To find out the minimum value based on multiple conditions with MINIFS score you need to use a formula.

The formula is: =MINIFS(C2:C4,A2:A4,”Group A”)

Applied the formula here.

Step 4: Press Enter, You will get the output.

The below image shows the result of the minimum value of group A.

Step 5: To find out the minimum score for Group B, need to use a formula.

The formula is: =MINIFS(C5:C7,A5:A7,”Group B”)

Applied the formula here.

Step 6: Press Enter, you will get the output.

The below image shows the result of the minimum value of group B which is 81 because 0 cannot be a minimum value.

Step 7: Now, use an asterisk wildcard to find the minimum score for all cricketers whose names start with A.  Asterisk allows you to match any string of any length.

For this the formula will be: =MINIFS(C2:C7, B2:B7,”A*”)

Step 8: Press Enter, You will get the output.

The below image shows the result of the minimum value of the name starting with A.

6. Finding the minimum value through a partial match with MINIFS

Step 1: First, you need to enter their data into an Excel spreadsheet to get an organized table of data as shown in the image.

Placed the data here.

Step 2: Now you want to find out the minimum value of Mango juice from between column A2 and A6, so add another column in D1:D4 to get the result there.

Added another column here.

Step 3: To find out the minimum value through a partial match of Mango Juice you need to use a formula. The formula is: =MINIFS(B2:B7,A2:A7,”Mango Juice”)

Applied the formula here. This formula matches only a single character which is what you exactly ask for. Here is the result.

Step 4:  Here you can see, that the output is based on all of the mango juice sold in the column, this asterisk wildcard to find the minimum sold for all the mangoes.

The result is outlined below.

Step 5:  Asterisk allows you to match any string of any length among the whole table which means not only the mango juice it can include green mango juice as well. For this the formula will be: =MINIFS(B2:B7,A2:A7,”Mango Juice*”)

Applied the formula here.

 

Step 6:  Here you can see the output is based on any string of the table.

Here is the result.

7. A few notes about the MINIFS Function

• #VALUE!

error – Occurs if the min_range and Criteria_range arrays are not of equal length.

• #NAME?

error – Occurs when we use Excel versions older than 2019 that do not support the function.

The MINIFS function is not ca.

• When comparing Criteria_range values ​​with criteria, the text strings “TEXT” and “text” will be considered a match.

It has the ability to handle up to 126 pairs of criteria_range and criteria arguments.

Application of MINIFS Function in Excel

• Finding Minimum Value: MINIFS can be used to find the smallest value in a range that meets specified criteria.
• Conditional Minimum: It helps identify the lowest value based on multiple criteria specified by the user.
• Dynamic Ranges: MINIFS can work with dynamic ranges, adjusting automatically as data changes.
• Data Analysis: Useful for data analysis tasks where finding the minimum value under certain conditions is required.
• Complex Conditions: Allows for complex conditions to be set, enabling precise data filtering.
• Combined Functions: Often used in combination with other functions like SUMIFS or AVERAGEIFS for comprehensive data analysis.

You may be interested:

1. Financial Dashboards
2. Sales Dashboards
3. HR Dashboards
4. Data Visualization Charts