Rank Cells Using Large/Small based on a "smart" Range

[filipm123]

hi all,

been building out a stock screening spreadsheet. I want to rank companies with upcoming ex-dividend dates in the next week or so. I am half way there ( I think, but if someone has a better way or has done this before, please let me know!!)
<P>
so you have a list of companies and ex-dividend dates. Column C is an if statement that if date is equal to today's date + 1 day but not greater than + 10 days

<P>

=IF(AND(b1>now()+1,B34<now()+10),"DIV","")

<table>
A B C
GE 6/19/2008 DIV
APPLE 7/12/2008
GM 6/21/2008
Chase 6/15/2008
J&J 6/23/2008 DIV
Pepsico 9/24/2008
Verizon 10/1/2008
ATT 12/5/2008
Google 6/27/2008 DIV
Etc 12/28/2008 DIV
Etc 6/29/2008 DIV
Etc 5/30/2008
Etc 7/1/2008
</table>
so now you need to set up a ranking of these companies and their respective ex-dividend dates. You should ONLY rank cells in Column B if Column C has a value of "DIV"
=small(SmartRange,1)
I realize an array formula is probably best here, just been blanking out!

Any ideas guys and gals? thanks!

 

[konew1]

The first company "ETC" has December date but DIV in col C. I assume that is just a typo.

This will rank the dates where there is DIV in Col C, but it ranks against the entire list, not just the ones tagged DIV.
=IF(C1="","",RANK(B1*(C1="DIV"),($B$1:$C$13),1))

If you change the result of the IF statements in column C to return the dividend date and not the text DIV you could then easily rank on column C alone

 

[filipm123]

The problem of ranking the entire list of dates is exactly the problem i was running into to!! - but why didnt I think of that. Simply return the dates I want in Column C and then rank on those. Ingenious!! - ha, thanks so much for the help. Its obvious that I am running on empty right now. Appreciate the help konew1..