Today, we are going to discuss the use of the MIN and IF function in Excel. We will use the MIN and IF functions together to find the minimum value from a list of values by applying certain criteria. This conditional searching will allow users to find the least value from a large dataset, for example: the lowest revenue from a business or the lowest grades of a particular student or the fastest lap by a runner etc. Here, we have the minimum sales of shirts from their store in Paris among three brands by using the MIN and IF functions in combination.
How to Use Combined MIN and IF Functions in Excel: 2 Ways
In this article, we will show the use of the MIN and IF functions in combination and the MINIFS function to get an output based on conditions. Here, we have the sales data of three different brands for two different products along the location of their stores. We will apply a single criterion and multiple criteria on this dataset to find the lowest number of cells.
1. Determining Minimum Sales Combining Excel MIN and IF Functions
In this method, we will use the MIN and IF function together to find out the minimum sales value from the dataset in Excel. We will apply single or multiple conditions to find the value.
1.1 Imposing Single Criteria
In this instance, we will find out the least amount from the cells in the store in Paris. Here, Paris is our criterion.
Steps:
- To begin with, select the H5 cell and type,
=MIN(IF(D5:D10=H4,E5:E10))- Then, press Enter.
- As a result, we will get the least sales of the store in Paris.
Read More: How to Find Lowest 3 Values in Excel
1.2 Implying Multiple Criteria
Here, we will imply two conditions on our dataset to find out the lowest sales. We will look for the lowest sales of shirts in the Paris store.
Steps:
- At the start, choose the H6 cell and enter,
=MIN(IF(B5:B10=H4,(IF(D5:D10=H5,E5:E10))))- Then, hit Enter.
- Consequently, we will have the minimum sales number of shirts from the Paris store.
Formula Breakdown
- IF(D5:D10=H5,E5:E10)—> This formula matches values from the D5:D10 range with the value in the H5 cell which is Paris. Then, it returns the Sales value corresponding to Paris from the E5:E10 range and for other values in the E5:E10 range it returns False.
- Output: {40000;FALSE;32000;40000;FALSE;FALSE}.
- IF(B5:B10=H4,(IF(D5:D10=H5,E5:E10)))—> This formula matches the value in the H4 cell which is Shirt, with the values in the B5:B10 cells and returns the values if true and otherwise returns False.
- Output: {40000;FALSE;32000;40000;FALSE;FALSE}.
- MIN(IF(B5:B10=H4,(IF(D5:D10=H5,E5:E10))))–> In this formula, the MIN function returns the minimum value from the previous array.
- Output: $32000
Read More: How to Find Minimum Value Based on Multiple Criteria in Excel
2. Using MINIFS Function
In this final method, we will use the MINIFS function. This is the default function of Excel to find the minimum value from a dataset by applying multiple criteria. Here, we will find out the minimum sales of Tommy Hilfiger in the Paris store.
Steps:
- Firstly, click on the H6 cell and enter the following formula,
=MINIFS(E5:E10,C5:C10,H4,D5:D10,H5)- Next, press the Enter button.
- As a result, we will have the lowest sales of Tommy Hilfiger from the store in Paris.
The MINIFS function is the latest edition of Excel. This allows users to impose multiple conditions to find the least value from a list. Unlike the previous method where we used the Nested IF inside the formula, this is more compact and least prone to mistakes.
Read More: How to Find Minimum Value in Excel
Download Practice Workbook
You can download the practice workbook here.
Conclusion
In this article, we used the MIN and IF functions in combination in 2 methods. This allows users to apply condition(s) while searching for the least value from a dataset. If you have any questions regarding this essay, feel free to let us know in the comments.
