Two Criteria MIN function

[jlepard]

Hi,

I kindly seek the wisdom of the excel gurus. I am stuck and I would sincerely appreciate some guidance.

Here is my issue:

I am analyzing quotations from multiple vendors, for multiple products.
Some of the quotes are valid, and others are deemed invalid (by me). I need a function to find the MIN value of the valid quotes for the given Product.

I am able to find the MIN value without taking into consideration the quote's validity, but doing so will give me false results. I could go through and manually remove all the invalid quotes, but I would prefer to do it with a formula.

I've tried using the AND function, but to no avail. (=MIN(IF(AND(Rangeof Products=SelectedProduct,RangeofValidquotes="yes"),Range of Quoted prices))

I've attached an example for more detail.

Thank you again!

J

[DonkeyOte]

Using AND clause in Arrays/Sumproducts is slightly different, eg:

=MIN(IF(($B$7:$B$15="A")*($F$7:$F$15="Yes"),$G$7:$G$15))
committed with CTRL + SHIFT + ENTER

so in basic syntax terms:

=MIN(IF((test1)*(test2),range))
CSE

or if preferred

=MIN(IF(test1,IF(test2,range)))
CSE

(no need to worry about FALSE condition as the Array will populate with Boolean FALSE as opposed to a number where result of IF not TRUE - and Booleans will be ignored in the MIN test)

[squiggler47]

=MIN(IF(($B$7:$B$15=B7)*($F$7:$F$15="Yes"),$D$7:$D$15,""))
CSE entered

[jlepard]

Awesome! thank you!

Approach number 1 dint work,
=MIN(IF(($B$7:$B$15=B7)*($F$7:$F$15="Yes"),$D$7:$D$15,""))

But approach number 2 was perfect.

THank you again,

J.