Rank if value meets a condition

[BJE1]

I'm trying to create a formula that ranks contract values, but only considers active contracts. Column A is the contract status (active/inactive), column B is the contract value, and column C would be the rank. If the contract is "inactive", I would want the rank to be null - such that if there were 1000 contracts and 500 were active, they would be ranked 1 - 500 and the inactive contracts wouldn't have rankings at all.

Any ideas?

 

[wangyuhu]

i think this: ( my english is very poor)
C1=IF(A1<>"active","",SUMPRODUCT((A$1:A$600="active")*(B$1:B$600>B1))+1)

 

[Peter_SSs]

Further to what wangyuhu has suggested, if you have Excel 2007 or later there is a more efficient formula. It also depends on whether your rank #1 is to be the highest or lowest value. Anyway, this should give you something to go on.

Excel Workbook
	A	B	C	D
1	Status	Value	Rank 2007+	Rank 2003-
2	Inactive	5
3	Active	8	1	1
4	Active	6	3	3
5	Active	3	5	5
6	Inactive	5
7	Active	4	4	4
8	Active	3	5	5
9	Inactive	5
10	Active	8	1	1
11
Rank

 

[BJE1]

That's brilliant! Had to study it a bit before the lightbulb went off and I figured out what the formulas everyone suggested were doing. Thanks again!