how to find the minimum in a range which satifies 2 sets of criterias?

[alice]

Hi


I have a huge dataset displayed in column format, ie each field is in columns. I need to do a look to match 2 columns and then once I found those rows, I want to find the minimum within those rows.


Ie say I need to find the minimum sales data for green apples.

Then I need check column A which gives me type of fruit = apples

And column B which gives me the colour = green


And say that rows H8:H40 fits that range, and column H contains the sales data, then I need the minimum number from H8:H40.


If this was a summation of all sales data then I can use the sumifs formula, but because I want the minimum, I am stuck on how to specify the range which matches the criteria to apply the min function to?


Please help!


Thanks

Alice

 

[DaveTurton]

Assuming the following:


You have header rows in row 1

Below the header rows there are 200 rows of data

Cell "I2" contains the fruit name being searched for

Cell "J2" contains the colour being searched for


enter the below formula in another cell (enter using Ctrl+Shift+Enter)


=MIN(IF((A1:A200=I2)*(B1:B200=J2),H1:H200))