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.
Method 1 – Determining Minimum Sales Combining Excel MIN and IF Functions
1.1 Imposing Single Criteria
Steps:
- Select cell H5 and enter:
=MIN(IF(D5:D10=H4,E5:E10))
- Press Enter.
- As a result, we have the lowest sales of the store in Paris.
Read More: How to Find Lowest 3 Values in Excel
1.2 Implying Multiple Criteria
Steps:
- Choose cell H6 and enter:
=MIN(IF(B5:B10=H4,(IF(D5:D10=H5,E5:E10))))- Press Enter.
- We get 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, 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
Method 2 – Using the MINIFS Function
Steps:
- Click on cell H6 and enter the following formula:
=MINIFS(E5:E10,C5:C10,H4,D5:D10,H5)- Press the Enter button.
- We get the lowest sales of Tommy Hilfiger from the store in Paris.
The MINIFS function is the latest edition of Excel. It allows users to impose multiple conditions to find the least value from a list. Unlike the previous method, which used the Nested IF inside the formula, this method is more compact and less prone to mistakes.
Read More: How to Find Minimum Value in Excel
Download the Practice Workbook
You can download the practice workbook here.
Related Articles
- How to Use MIN Function to Exclude Zero in Excel
- Excel MIN Function Returns 0
- Difference Between MAX and MIN Function in Excel
- How to Find Minimum Value That Is Greater Than 0 in Excel
- How to Find Lowest Value with Criteria in Excel
- How to Find Minimum Value with VLOOKUP in Excel
<< Go Back to Excel MIN Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
