=(SUMPRODUCT(--($B$5:$B$200=$B5),--($L$5:$L$200="Sell"),--(H5<H$5:H$200))+1)

The above isn't working

Colum B = labels like "Apples, Bannanas,"
Column L = lables like "Hold" or "Sell"
Column H = numbers that I would like ranked.

I would like this formula, which is in column M (starting at M5), to rank
numbers in column H from lowest to highest, but only when they are labeled
"Sell" in Column L. That way I can create a formula that makes sell
suggestions based on a rank: Sell 1, Sell 2, Sell 3, Sell 4, etc. (using
="Sell "&text(formula here,"0")

any suggestions?

thanks a lot!

Try it like this:

=IF(L5="Sell",SUMPRODUCT(--(B$5:B$200=B5),--(L$5:L$200="Sell"),--(H5H$5:H$200))+1,"")

--
Biff
Microsoft Excel MVP


"SteveC" wrote in message
...
=(SUMPRODUCT(--($B$5:$B$200=$B5),--($L$5:$L$200="Sell"),--(H5<H$5:H$200))+1)

The above isn't working

Colum B = labels like "Apples, Bannanas,"
Column L = lables like "Hold" or "Sell"
Column H = numbers that I would like ranked.

I would like this formula, which is in column M (starting at M5), to rank
numbers in column H from lowest to highest, but only when they are labeled
"Sell" in Column L. That way I can create a formula that makes sell
suggestions based on a rank: Sell 1, Sell 2, Sell 3, Sell 4, etc. (using
="Sell "&text(formula here,"0")

any suggestions?

thanks a lot!