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:

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.

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:

• MAXIFS Function in Google Sheets: Find Maximum Value Conditionally.
• MINIFS in Array Formula in Google Sheets (Examples).