MINIFS Function in excel

In this post, you’ll learn about MINIFS Function, its syntax and the way of using MINIFS Function in Microsoft Excel.

What is MINIFS Function?

 MINIFS Function in excel is a statistical function. The MINIFS Function calculates and returns the smallest numeric value that meets one or more criteria in a given range.

MINIFS can be used with criteria based on dates, numbers, text, and other conditions. MINIFS supports logical operators(>, <, <>, =) and wildcards(*, ?) for partial matching.

In financial analysis, the MINIFS Function is useful in extracting a minimum numeric value.

Syntax

=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)
Arguments
  • = – built-in function.
  • MINIFS() – function name.
  • min_range – range of values used to determine minimum values among the given data.
  • range 1(required value),range 2[optional] – the range arguments to calculate.
  • criteria 1(required argument), criteria 2[optional] – the criteria to use in range. Criteria can be number, expression or text that defines which cells will be evaluates as a minimum value.

MINIFS Function is in a group of eight functions in Excel that splits logical criteria into two parts(range + criteria).

Notes:

  • Conditions applied using range or criteria pairs.

criteria pairs – refers to the word that by a single sound.

Example- fan / van.

  • MINIFS can handle up to 126 range or criteria pairs.
  • #VALUE Error – if criteria range is not the same size as the min_range.
  • MINIFS Function returns 0, if no cells match criteria.
  • Ignores empty cells, even when criteria match.

REMARK:

MINIFS Function is a new function, available in Excel 365 and Excel 2019 but in earlier versions you have to use an array formula MIN and IF to find the minimum values with criteria.

How to use MINIFS Function in Excel ?

MINIFS Function calculates smallest numeric values with one or more criteria.

DOUBLE QUOTES (“”):

  • Text values are must be within double quotes, but numbers are not. If the logical operator with a number, the number and operator must be within double quotes.
  • Value from another cell – value from another cell by using catenation.
  • WILLCARDS:

The wildcards characters question mark (?), asterisk (*), or tilde (~) can used in criteria.

  • The question mark (?) matches any single characters.
  • The asterisk (*) matches any sequences of characters.
  • The tilde (~) is an escape character it allows you to find the literal wildcard.

Example:

Using MINIFS Function we can calculate the minimum score of 2nd years among the batch of 1st, 2nd and 3rd year students.

Step 1:

Open the workbook in your Microsoft Excel.

Step 2:

Enter the data, in the workbook.

How to use MINIFS Function in Excel ?
STEP 3:

In the new cell, give the formula of the function or the syntax. Start with ‘ =’ for every functions, MINIFS to initiate functions name, followed by open parenthesis.

How to use MINIFS Function in Excel ?

According to the arguments of the syntax, you have to calculate the min_range. Therefore the min_range value is from D5:D14.

According to the example, the min_range is the score of the students.

STEP 4:

After calculating min_range, next step is to calculate criteria_range 1.

How to use MINIFS Function in Excel ?

criteria_range 1 is from C5:C14.

According to the example, criteria_range 1 is year of students.

STEP 5:

After calculating criteria, next step is to calculate criteria 1.

How to use MINIFS Function in Excel ?

According to the example, criteria 1 is 2nd year.

STEP 6:

Press Enter to get the results.

How to use MINIFS Function in Excel ?

Returns 44, the minimum value among all the 2nd year students are 44.

Leave A Reply

Your email address will not be published. Required fields are marked *

You May Also Like

In this post, you’ll learn how to Find Blank cells in excel so that you can remove or delete the blank cells form...
In this article, you’ll learn what is a Gauge Chart in Microsoft Excel. Also, you will learn how to add...
Microsoft Excel provides a shortcut for the users to move columns in excel using two different ways – using Shift...