Rank only if not completed

[culle1bj]

Hello,

I am trying to rank projects that have not been completed. Once the project has been completed, I do not want to show it as a top priority. Here is the formula I am using:

=IF(T17="COMPLETE","",RANK(DB17,$DB$12:$DB$65,0)+COUNTIF($DB$12:DB17,DB17)-1)

This does not show a rank for completed projects, however, it still includes it in the total rank. For example, if I have 10 projects and 2 are completed, the lowest dollar value is still showing it's rank as 10. I need that to show 8.

Any suggestions?

Thanks,
Brad

 

[sijpie]

Excel 2010
	C	D	E	F	G
9			Your
10			formula	new column	Rank on
11			with issue		new column
12	Complete	45
13		12	8	12	6
14		18	5	18	4
15	Complete	13
16		15	6	15	5
17		78	1	78	1
18		46	2	46	2
19		25	4	25	3
Rank

Cell Formulas
Range	Formula
E12	=IF(UPPER(C12)="COMPLETE","",RANK(D12,$D$12:$D$65,0)+COUNTIF($D$12:D12,D12)-1)
F12	=IF(UPPER(C12)="COMPLETE","",D12)
G12	=IFERROR(RANK(F12,$F$12:$F$65,0)+COUNTIF($F$12:F12,F12)-1,"")

 

[barry houdini]

Here is the formula I am using:

=IF(T17="COMPLETE","",RANK(DB17,$DB$12:$DB$65,0)+COUNTIF($DB$12:DB17,DB17)-1)

Hello Brad,

Change your formula to this in row 12 copied down

=IF(T12="COMPLETE","",COUNTIFS($DB$12:$DB$65,">"&DB12,$T$12:$T$65,"<>COMPLETE")+COUNTIFS($DB$12:$DB12,DB12,$T$12:$T12,"<>COMPLETE"))