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

GiGiKing·March 12, 2024·0 comments

Do you want to learn how to use the MINIFS function in Google Sheets? This advanced function is incredibly useful when you need to retrieve the minimum value based on specific criteria.

The MINIFS function in Google Sheets allows you to find the minimum value that meets one or multiple conditions. It’s like having a magical tool that searches for the smallest value that matches your desired criteria.

In this article, we will dive deep into the MINIFS function and explore its syntax, arguments, and various use cases. By the end, you’ll be a pro at using this powerful function in Google Sheets.

MINIFS Function Syntax and Arguments

Before we dive into the examples, let’s understand the syntax and arguments of the MINIFS function:

Syntax:
MINIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Arguments:

  • range: This is the range of cells from which the minimum value will be retrieved. Make sure it’s a physical range and not an expression like an IMPORTRANGE formula.
  • criteria_range1: This is the criteria range of the same size as the range where criterion1 will be evaluated.
  • criterion1: This is the pattern or test that defines which cells will be evaluated as the minimum.
  • [criteria_range2, criterion2, ...] (Optional): You can include additional criteria ranges and their associated criteria.

The first three arguments (range, criteria_range1, and criterion1) are essential for the MINIFS function to work. Specify the range, criteria range(s), and the criterion(s) you want to use.

Basic Example

Let’s start with a basic example to understand how the MINIFS function works. Suppose you have a list of people’s names in column A and their ages in column B. You want to find the lowest age in column B.

In this case, you can simply use the MIN function to retrieve the lowest age:

=MIN(B2:B)

However, what if you want to apply a specific condition? Let’s say you want to find the lowest age in the age group above 29. This is where the MINIFS function comes to the rescue.

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

In this formula:

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

Here’s a screenshot illustrating the result:

Example Screenshot

Advanced Use of MINIFS Function in Google Sheets

Now that you understand the basic usage of the MINIFS function, let’s explore some advanced use cases. Consider a sample sheet that contains country names in column A, apple fruit production in tonnage in column B, and continent names in column C.

We’ll use this sample data to find answers to the following questions:

Two Basic Questions:

  1. What is the lowest production quantity of apples in Asia?
  2. What is the lowest production quantity of apples in continents other than Asia?

Two Advanced Questions:

  1. What is the lowest production quantity of apples in Asia, excluding Iran?
  2. What is the lowest production quantity of apples in both Asia and Europe?

To find the lowest production quantity of apples in a specific continent, such as Asia, you can use the following MINIFS formula:

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

This falls under the basic usage of the MINIFS function.

For the second question, i.e., finding the lowest production quantity of apples in continents other than Asia, you can use the following MINIFS formula:

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

To answer the third and fourth questions, we need to explore advanced MINIFS usage. These questions involve both AND and OR logic, without relying on the same logical operators.

MINIFS with AND Criteria (Multiple Criteria in Different Columns)

Let’s consider the question: What is the lowest production quantity of apples in Asia other than Iran? In this case, we need to test the criteria in two columns: the country (A9:A) and continent (C9:C) columns. Two conditions need to be met.

Here is the formula:

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

In this formula:

  • 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 example showcases the use of AND logic in the MINIFS function in Google Sheets.

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

The last question is: What is the lowest production quantity of apples in two continents, Asia and Europe?

There are two options to solve this. The first option is to use two MINIFS functions wrapped by a MIN function:

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

In this formula, the first MINIFS formula returns the minimum quantity in Europe, and the second one returns the minimum quantity in Asia. The outer MIN function then returns the minimum value from these two.

However, here’s an alternative and recommended approach to achieve the same result:

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

In this formula:

  • 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 formula. Remember to separate each criterion with a pipe delimiter.
    • The formula provided is for case-sensitive and partial matches. Replace "Asia|Europe" with "(?i)Asia|Europe" to make it case-insensitive, or "(?i)^Asia$|^Europe$" to make it case-insensitive and an exact match.

By using the MINIFS function with OR logic, you can easily find the lowest production quantity of apples in multiple continents.

MINIFS Function Criteria Usage Tips in Google Sheets

In the previous examples, we explored the usage of text criteria, number criteria, and comparison operators in the criterion part. Now, let’s discuss how to use date, time, and timestamp criteria in the MINIFS function.

Here are some tips 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. For example: "="&DATE(year, month, day).

Can we use wildcard characters in the MINIFS function for partial matching in Google Sheets? Absolutely! Feel free to refer to our tutorial on “Three Main Wildcard Characters in Google Sheets Formulas” for more information.

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 number, text, date, and timestamp criteria in the correct format.

Most Common Errors in the MINIFS Function in Google Sheets

We’ve covered everything you need to master the MINIFS function in Google Sheets. However, let’s not forget about error handling. While the MINIFS function is not complex, you may encounter a few errors if you’re not careful. Here are some common errors and how to troubleshoot them:

  • VALUE!: This error occurs when there are mismatched ranges. Make sure the number of columns and rows are equal in both the range and criteria ranges. This error often happens when you use non-array functions with criteria ranges like REGEXMATCH, DATE, EOMONTH, etc. To solve this, include the ARRAYFORMULA function.
  • #N/A: If you encounter this error, check if any argument is missing in the formula. The MINIFS formula must contain a range, a criteria range, and a criterion. If you have two criteria ranges, make sure you specify the criterion accordingly.
  • #DIV/0: This error typically occurs when there are errors in the range. Double-check your data to ensure there are no errors.
  • If you wrap the MINIFS formula with the IFERROR function, you won’t be able to see the error value, which might hinder your troubleshooting.

That’s it! Now you have all the know-how to confidently use the MINIFS function in Google Sheets. Start exploring its advanced capabilities and unlock the true potential of your data analysis.

To learn more tips and tricks about Google Sheets, check out Crawlan.com, where you’ll find a wealth of information to enhance your spreadsheet skills.

GiGiKing

Related posts