HomeGoogle DocsSpreadsheetHow to Use MINIFS Function in Google Sheets

How to Use MINIFS Function in Google Sheets [Advanced Use]

Published on

Do you know when to use the MINIFS function in Google Sheets? It proves helpful when you want to retrieve the minimum value based on specific criteria.

The term ‘MIN IF’ aptly describes this function, as MINIFS returns the minimum value based on a condition (IF) or conditions (IFS) met.

Learn how to use the MINIFS function in Google Sheets with the assistance of a few real-life examples and additional tips. We will start with the syntax, which is the starting point for understanding any function.

MINIFS Function Syntax and Arguments

Syntax:

MINIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

Arguments:

  • range: The ‘physical’ range of cells from which the minimum will be retrieved. It should not be an expression, such as an IMPORTRANGE formula.
  • criteria_range1: The criteria range of the same size as the range over which to evaluate criterion1.
  • criterion1: The pattern or test that defines which cells will be evaluated as a minimum.
  • criteria_range2, criterion2, (Optional): Additional criteria ranges and their associated criteria.

The first three arguments in the MINIFS function are essential for it to perform. Specify a range, a criteria range (which can be the same or different), and a criterion.

Basic Example

Here, I have the names of a few people (A2:A) and their ages (B2:B). We can use the MIN function to retrieve the lowest age (smallest value) in the range B2:B.

=MIN(B2:B)

However, in this case, I want to apply a specific condition. What is that?

I aim to find the lowest age in the age group above 29. Let’s see how we can achieve that using the MINIFS function in Google Sheets.

Formula:

=MINIFS(B2:B, B2:B, ">29")

Where:

  • B2:B is the range and criteria_range1
  • ">29" is the criterion1 (alternatively, you can express the criterion as ">=30")

Please see the screenshot for an illustration:

Example 1: Same Range and Criteria Range in MINIFS Function in Google Sheets

Advanced Use of MINIFS Function in Google Sheets

Here is a different sample sheet that contains country names in column A (A9:A), Apple fruit production in tonnage in column B (B9:B), and continent in column C (C9:C).

These are just mock-up data for testing the real-life use of the MINIFS function.

Example 2: Separate Range and Criteria Range in MINIFS Function in Google Sheets

Let’s find answers to the following using the MINIFS function in Google Sheets:

Two Basic Questions:

  1. Find the lowest production quantity of Apple in Asia.
  2. Find the lowest production quantity of Apple in continents other than Asia.

Two Advanced Questions:

  1. Find the lowest production quantity of Apple in Asia other than Iran.
  2. Find the lowest production quantity of Apple in Asia and Europe.

To find the lowest production quantity of Apple in any specific continent, here Asia, we can use the below MINIFS formula:

=MINIFS(B9:B, C9:C, "Asia")

Actually, this falls under the basic usage of the MINIFS function.

The second question, i.e., the lowest production quantity of Apple in continents other than Asia, is also easy to answer using the following MINIFS formula.

=MINIFS(B9:B, C9:C, "<>Asia")

To find answers to the third and fourth questions, you need to explore advanced MINIFS usage as they involve both AND and OR logic, without relying on the same logical operators.

MINIFS with AND Criteria (Multiple Criteria in Different Columns)

The question is to find the lowest production quantity of Apple in Asia other than Iran. So, we need to test the criteria in two columns: the country and continent columns. Two conditions need to be met. Here is the formula:

=MINIFS(B9:B, A9:A, "<>Iran", C9:C, "Asia")

Where:

  • B9:B is the range.
  • A9:A is the criteria_range1.
  • "<>Iran" is the criterion1.
  • C9:C is the criteria_range2.
  • "Asia" is the criterion2.

This is an example of AND logic in the MINIFS function in Google Sheets.

MINIFS with OR Criteria (Multiple Criteria in the Same Column)

The last question is to find the lowest production quantity of Apple in two continents: Asia and Europe.

Here, there are two options. Either use two MINIFS wrapped by a MIN or REGEXMATCH within MINIFS. We will explore both options.

=MIN(MINIFS(B9:B, C9:C, "Europe"), MINIFS(B9:B, C9:C, "Asia"))

Here, the first MINIFS formula returns the minimum quantity in Europe, and the second one returns the minimum quantity in Asia. The outer MIN returns the minimum from these two.

Here is the alternative and my recommended approach for the MINIFS function with OR logic in Google Sheets.

=ArrayFormula(MINIFS(B2:B, REGEXMATCH(C2:C, "Asia|Europe"), TRUE))

Where:

  • B9:B is the range.
  • REGEXMATCH(C9:C, "Asia|Europe") is the criteria_range1.
    • This formula returns TRUE wherever the criteria match and FALSE elsewhere. We have included the ARRAYFORMULA function to support this formula.
    • If you have more than two criteria, include them within the REGEXMATCH. Don’t forget to separate each criterion with a pipe delimiter.
    • The formula is for case-sensitive and partial matches. Replace "Asia|Europe" with "(?i)Asia|Europe" to make it case-insensitive and "(?i)^Asia$|^Europe$" to make it case-insensitive and an exact match.
  • TRUE is the criterion1.

MINIFS Function Criteria Usage Tips in Google Sheets

In the above examples, we have seen the usage of text criteria, number criteria (numeric values), and comparison operator usage in the criterion part. How do we use the date, time, and timestamp criteria in the MINIFS function in Google Sheets?

Here are the tips you can follow to apply criteria in a time range, timestamp, or date range:

  • Specify dates using the DATE function with the syntax DATE(year, month, day).
  • Specify the time using the TIME function with the syntax TIME(hour, minute, second).
  • To specify a timestamp, combine date and time as a timestamp using the syntax DATE(year, month, day)+TIME(hour, minute, second).

Notes:

  1. Avoid placing double quotes around these types of criteria.
  2. To include the comparison operator, you can combine it with the criterion. Here is an example: ">"&DATE(year, month, day)

Can we use wildcard characters in the MINIFS function for partial matching in Google Sheets?

Yes, you can. Please refer to the following tutorial: Three Main Wildcard Characters in Google Sheets Formulas.

The easiest way to specify criteria in the MINIFS function is to enter them in cells and refer to those cells in the formula. This ensures that you use the number, text, date, and timestamp criteria in the correct format.

Most Common Errors in the MINIFS Function in Google Sheets

I’ve shared everything one needs to master the MINIFS function in Google Sheets. The only thing left is error handling.

MINIFS is not a complex function. You might not encounter errors if you use it carefully. Here are some of the errors that you may encounter when you use this function.

VALUE!: Mismatched ranges. Ensure that the number of columns and rows are equal in the range and criteria ranges. This error may often happen when you use non-array functions with criteria_range, such as REGEXMATCH, DATE, EOMONTH, etc. Include the ARRAYFORMULA function to solve it.

#N/A: Check if any argument is missing. The formula must contain a range, a criteria range, and a criterion. If there are two criteria ranges, you must specify the criterion accordingly.

#DIV/0: Check for errors in the range.

If you wrap the formula with the IFERROR function, you won’t be able to see the error value, which might hinder troubleshooting.

Related:

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

2 COMMENTS

  1. Trying to use MINIFS with criteria that fall in the same range, but need to make it where one of the criteria might not be present. How can this be set?

    • Hi, Leandro,

      Thanks for pointing out the issue which I haven’t foreseen.

      You can use FILTER instead of MINIFS to solve the issue.

      =ifna(min(filter(B9:B21,(C9:C21="Asia")+(C9:C21="Europe"))))

      From your other comment, which I haven’t published, I understand that you were trying to use DMIN.

      When using the DMIN function, include the field labels. So B9:B21 will become B8:B21, and the formula is;

      =dmin(A8:C21,2,{"Continent";"Asia";"Europe"})

      If the output column contains dates instead of numbers, you must wrap the above formula with to_date() or format the output to date (Format > Number > Date).

      The issue with DMIN is if none of the criteria match, it will return 0. Formatting that number to date may lead to 30/12/1899.

      So better use the above filter.

LEAVE A REPLY

Please enter your comment!
Please enter your name here