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

0
167
How to Use MINIFS Function in Google Sheets

Do you know where to use MINIFS function in Google Sheets? When you want to return a minimum value based on certain criteria you can use MINIFS. I think the word MIN IF also justifies this function as MINIFS returns minimum value based on a condition (IF) or conditions (IFS) met. You can learn here how to use MINIFS function in Google Sheets with the help of few real life examples. You may also want to check my previous Google sheets tutorials featuring similar functions like MIN, MINA and SMALL.

MINIFS Function in Google Sheets – Syntax, How to and Examples

MINIFS Syntax:

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

As usual let me explain the arguments in the syntax with few examples.

MINIFS Examples and How to:

We are going to learn how to use MINIFS in Google Sheets. I’ve included basic as well as advanced MINIFS formulas in this tutorials.

Example # 1:

Here I have the name of few peoples and their age. By using MIN function you can return the lowest age in the range B2:B6. But here I want to apply one condition. I want to find the lowest age in the age group above 29.

Example 1: MINIFS Function in Google Sheets

Let me explain the MINIFS formula which I’ve used here with the help of the syntax arguments. See the colour patterns to understand the formula.

Formula: =minifs(B2:B6,B2:B6,“>=30”)

Arguments: Range, Criteria 1 Range, Criterion 1

MINIFS formula explanation:

As per the syntax, you can see that B2:B6 is the range here. This range contains the value from which we want to find the minimum age. Then the criteria range can be from the same range or from any other range. Here our minimum value range and criteria range are the same. Our criterion here is “>=30”.

Example # 2 – Advanced Use of MINIFS in Google Sheets

Let me take you to another example to MINIFS. In this example, I’m going to use Google Sheets MINIFS function with little variation as compared to the above. Here the criteria range is different.

Example 2: MINIFS Function in Google Sheets

Here the data range contain the name of some of the countries who are the largest producer of Apple fruit in the world. Thanks to Google Sheets IMPORTHTML function. With the help of this function, I’ve imported the data from a web page. When you analyse this data, you can see that one country, China, surpassed others in production. Also you can see that the countries are from different continents. Here what I want to find is the lowest producer of Apple fruit in Asia. Since the data range is too small, you can find the name manually and it’s Iran.

minifs example chart

Note: Please Check our Charts section to learn how to create different charts in Google Sheets.

How to return the name of this country using MINIFS as the role of MINIFS is to return the minimum value in a range of cells, filtered by a set of criteria?

Here is that steps. First we have to find the minimum value based on condition using MINIFS.

Formula to find the lowest quantity of Apple producer in Asia.

Formula: =minifs(B9:B21,C9:C21,”ASIA”)
Result: 1335480

In this example I’ve set C9:C21 as criteria range 1 and “Asia” as criterion 1. This formula would return the value 1335480. If you go through the data you can find the name of the country who produces these much quantity of Apple fruit and it’s Iran. I want to return this name.

That means you are going to learn how to use MINIFS to find the lowest quantity based on criteria and return the value from the same row. Here it’s.

Here we should use a lookup formula. No, Vlookup can’t be useful here as the lookup value in our example is not in the first column. So here the solution is Index – Match Combo. Please check our Index and Match combo tutorial and come back.

Below formula would return the name of the country who’s the lowest producer of Apple fruit in Asia.

Formula: =index(A9:C21, match(minifs(B9:B21,C9:C21,”ASIA”),B9:B21,0),1)
this formula is equal to
Formula: =index(A9:C21, match(1335480,B9:B21,0),1)
Note: type the formula in your sheet, do not copy as it may not work.

Example # 3 – Usage of MINIFS with Criteria Falls in Same Range Twice

Here is my last example to how to use MINIFS function in Google Sheets. But here I’m adopting a different approach.

When you have the MINIFS criteria in same column twice, you can use the following method. This method is similar to the usage of OR logical function with MINIFS.

I want to find the lowest producer of Apple fruit from Asia and Europe. The formula here is;

=min(minifs(B9:B21,C9:C21,“ASIA”),minifs(B9:B21,C9:C21,“Europe”))

It’s simple to learn. It’s actually two MINFIS formulas. One to find the lowest producer in Asia and the other in Europe. With a Min function we can then return the lowest from these two countries. That’s all. Hope you could learn how to use MINIFS in Google Sheets. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here