How to Use Combined MIN and IF Function in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Illustrating Usage of the MIN and IF Function in Excel


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.

Illustrating Usage of the MIN and IF Function in Excel


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.

Using MIN and IF Functions with a Single Criterion

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.

Applying MIN and IF Function with Multiple Criteria

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.

Utilizing MINIFS Function

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Adnan Masruf
Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo