On Jul 6, 4:51*am, "JoeU2004" wrote:
PS....

I wrote:
You should use the other formula:
=MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43))

Alternatively:

=MIN(IF(A2:A43="d",IF(B2:BG43<"",B2:BG43)))

That might be easier to understand. *However, beware that that does not
scale well due to nesting limits before Excel 2007. *That is, the number of
ANDed conditions is limited to 7, and it is difficult to combine ANDed and
ORed conditions.

----- original message -----

"JoeU2004" wrote in message

...

"Rene" wrote:
=MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43))
This returns the min in B2:BG43. *I'm looking for the min
in B2:BG43 with a "d" in A2:A43.

You misunderstood. *I said that formula does __not__ work.

You should use the other formula:

=MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43))

I presented the first formula (with AND) to help you understand the second
formula.

----- original message -----

"Rene" wrote in message
...
=MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43)) This returns the min in
B2:BG43. *I'm looking for the min in B2:BG43 with a "d" in A2:A43.

Thanks

"JoeU2004" wrote:

"Rene" wrote:
MIN(IF(A2:A43="d",B2:BG43)) entered as an array
[....] how do I ignore the blank cell?

You probably already discovered that the "obvious" solution does __not__
work, namely the following array formula:

=MIN(IF(AND(A2:A43="d",B2:BG43<""),B2:BG43))

But the following array formula expresses the same logic:

=MIN(IF((A2:A43="d")*(B2:BG43<""),B2:BG43))

----- original message -----

"Rene" wrote in message
...
MIN(IF(A2:A43="d",B2:BG43)) entered as an array

Does it return a 0 because of the blank cell? And how do I ignore the
blank
cell?

a2 b2 * * * * *c2
d * blank * *120
d * 100
d * 150 * * *175
a * * 75 * * * *90

Thanks

Hi - I was not part of this discussion, but your thread has just
helped me A LOT - massively saved face at my client - thanks for your
help all!

JohnOnTheInternet