Find Minimum Value Using Criteria In Excel

Deepanshu Bhalla Add Comment ,

This tutorial explains how to find the smallest value in Excel based on a single or multiple conditions.

Suppose you have data for products, sales and regions. You are asked to figure out the minimum sales generated by Product B. The data is shown in the image below.

Find Minimum Value Using Criteria In Excel
Download the workbook

Assuming you have data in range B3:D14. (See the image below)

Example: Find Minimum Value Using Criteria In Excel

To calculate the minimum sales generated by Product B.

=MIN(IF(B3:B14="Product B",D3:D14))
Since the above formula is an array formula, press CTRL SHIFT ENTER to confirm it instead of simply pressing the ENTER key.

After pressing CTRL SHIFT ENTER, the formula will be surrounded by curly braces. It will look like:

{=MIN(IF(B3:B14="Product B",D3:D14))}
  • B3:B14: It represents the cells B3 to B14, which contains product information.
  • "Product B": This is the condition being tested. It checks if the corresponding cells in the range B3:B14 contain the text "Product B".
  • D3:D14: It represents the cells D3 to D14, which contains sales figure related to the products mentioned in column B.
  • IF(B3:B14="Product B",D3:D14): This part of the formula creates an array of values. It goes through each cell in the range B3:B14 and checks if the corresponding cell contains the text "Product B". If it does, it returns the value from the corresponding cell in the range D3:D14. Otherwise, it returns FALSE or an error.
  • MIN(IF(B3:B14="Product B",D3:D14)): Finally, the MIN function is used to find the minimum value from the array of values created by the IF function. It will identify the smallest value among the numbers returned by the IF function, which is the minimum value in column D (D3:D14) where the corresponding cell in column B (B3:B14) contains "Product B".

Find the Minimum Value in Excel with Multiple Criteria

To calculate the minimum sales generated by Product B in East region.

Find the Minimum Value in Excel with Multiple Criteria
=MIN(IF((B3:B14="Product B")*(C3:C14="East"),D3:D14))
Since the above formula is an array formula, press CTRL SHIFT ENTER to confirm it instead of simply pressing the ENTER key.

After pressing CTRL SHIFT ENTER, the formula will be surrounded by curly braces. It will look like:

{=MIN(IF((B3:B14="Product B")*(C3:C14="East"),D3:D14))}
  • (B3:B14="Product B")*(C3:C14="East"): This part of the formula combines the two conditions using multiplication. The * (asterisk) acts as the AND operator in array formulas. So, this expression creates an array of TRUE and FALSE values, where each element corresponds to whether the conditions are met for the corresponding row.
  • IF((B3:B14="Product B")*(C3:C14="East"),D3:D14): This part of the formula creates another array of values. It goes through each cell in the range B3:B14 and C3:C14 and checks if both conditions are true for that row. If both conditions are true, it returns the value from the corresponding cell in the range D3:D14. Otherwise, it returns FALSE or an error.
  • MIN(IF((B3:B14="Product B")*(C3:C14="East"),D3:D14)): Finally, the MIN function is used to find the minimum value from the array of values created by the IF function. It will identify the smallest value among the numbers returned by the IF function, which is the minimum value in column D (D3:D14) where the corresponding cells in columns B and C (B3:B14 and C3:C14) meet the specified conditions ("Product B" in column B and "East" in column C).
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

0 Response to "Find Minimum Value Using Criteria In Excel"
Next → ← Prev