If you are working with a set of data in Google Sheets and you want to find the minimum value in a range of cells based on a set of criteria, the MINIFS function is something you should take the time to learn.
In this tutorial, I will show you how to use the MINIFS function in Google Sheets.
The MINIFS function will take a range of cells and return the minimum value based on a set of criteria that you specify in the formula.
In other words, this formula allows you to filter a dataset by a set of criteria, and then return the minimum value from the filtered range.
The syntax of the MINIFS function is:
=MINIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, …])
- range – the range of cells that you want to return the minimum value for
- criteria_range1 – the range of cells to evaluate criterion1 for
- criterion1 – the condition or test to apply to criteria_range1
- criteria_range2 – This is an optional argument. You can continue to add additional criteria ranges here
- criterion2 – if you use additional criteria ranges, this will be the condition or test to apply to your additional ranges
Using the MINIFS Function
Here are the basic steps to use the MINIFS function:
- In the first argument of the function, put in the range that you want to return the minimum value from
- In the second argument of the function, place the range that will contain the values you are filtering by with your criteria
- As the third argument, put in the criteria that you are filtering by
- If you want to filter by additional criteria you can continue to put in additional criteria ranges and criterion
Here is an example of how this formula is setup:
In the above example, the exact formula that I use is:
This formula will return the minimum value from range C2:C11, for cells that contain “Home” in cells A2:A11. You can see that the smallest value in the range for that criteria is 857, so that is the value that the formula returns.
You can also use MINIFS with multiple criteria.
This time the formula is set up with two sets of criteria. It is returning the smallest value in range D2:D10 where range B2:B10 is equal to “Clothing” and range A2:A10 is equal to “June”.
By now you should have a good understanding of what the MINIFS function does, and how to set up this formula in your own spreadsheet.
As you gain more comfort with this function, you can begin to use it in more powerful ways by combining it with other functions and using comparison operators as your criterion for even more advanced filtering options