In ,
Pete_UK spake thusly:

No, you don't need a sumproduct - the * is equivalent to AND.
Basically the formula is saying if all three conditions are met, then
take the value from column N otherwise take a very large value, and do
this for every cell in the ranges. Then, with the array built up, take
the minimum of those numbers.

I got it to work now! Thanks again, Pete. Not entirely sure what I
did wrong the first time. I will not for those following along that
an errant "-" got intorduced in your correction, though. I took it
out. I also changed "10^10" to "" -- I am content to have nothing there
if there is now price for the item in question.

Very helpful -- thanks!

Dallman

------------------------------------------
If you are getting #NAME? error, then you may have mis-typed MIN or
IF, or you may have missed the quotes around "HOV", "STK" or "SELL",
or you may have missed the : from between one of the ranges, or missed
one of the brackets.

Don't forget, you must commit the formula with CSE after you amend it.

Hope this helps.

Pete

On Dec 8, 12:40 pm, Dallman Ross <dman@localhost. wrote:
In ,
Pete_UK spake thusly:

Sorry, missed a bracket from the end:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10)-)

Looks promising, Pete, but I'm getting a #NAME? error. I'm thinking the
problem may lie with the "*" between text columns. Don't we need a SUMPRODUCT
in there somewhere, anyway?

Dallman

------------------------



On Dec 8, 2:00 am, Pete_UK wrote:
Try this array* formula:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10)

All ranges must be the same size - I've assumed you have 1000
elements.

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit
it, rather than the normal ENTER. If you do this correctly then Excel
will wrap curly braces { } around the formula when viewed in the
formula bar - you must not type these yourself.

Hope this helps.

Pete

On Dec 8, 1:23 am, Dallman Ross <dman@localhost. wrote:

I'm stuck and could use some help. I have a table with lots of
columns, one of which -- N -- is a price. I want to find the
minimum price where the value in Column A is "HOV" and Col. B is
"STK" and Col. K is "SELL".

Thanks for the quick hand at this, guys.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -