RANK by two columns to exclude rows with a specific cell value in a third column

[janetk411]

	A	B	C	D
1	BRANCH	CLIENT	FILECNT	RANK
2	12	ABRE01	50	1
3	12	AEVA01	45	2
4	12	NIEX01	46
5	12	NIEX02	47
6	12	CAI00	29	3
7	32	ABRE02	15	3
8	32	AEVA01	45	1
9	32	NIEX05	9
10	32	NIEX07	47
11	32	CAVI00	40	2

<tbody>
</tbody>

The table above represents the number of files, by client, by branch. I want to rank the FILE COUNT number in descending order, by BRANCH but I do not want to include the client codes that begin with the first four letters NIEX in the ranking.
The RANK column represents the ranking that the formula should create.
Can someone help me with a formula to auto create the ranking in the RANK column?
I appreciate any assistance some one can give me.
Thank you in advance.

 

[CalcSux78]

Since Excel doesn't have a RankIf function, you could use a Countifs function.

Excel 2010

	A	B	C	D
1	BRANCH	CLIENT	FILECNT	RANK
2	12	ABRE01	50	1
3	12	AEVA01	45	2
4	12	NIEX01	46
5	12	NIEX02	47
6	12	CAI00	29	3
7	32	ABRE02	15	3
8	32	AEVA01	45	1
9	32	NIEX05	9
10	32	NIEX07	47
11	32	CAVI00	40	2

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =IF(LEFT(B2,4)="NIEX","",COUNTIFS(A:A,A2,C:C,">"&C2,B:B,"<>NIEX*")+1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[janetk411]

Yeah! Thank you. That did it but..... this allows for duplicate ranking. Do you know how to change this so each rank is unique?

 

[CalcSux78]

You could create a Tie-Breaker column based off of whatever criteria you'd prefer (alphabetical, 1st in list, etc)

Here's an article on it that may help: https://exceljet.net/formula/break-ties-with-helper-column-and-countif
An article on Rank if: https://exceljet.net/formula/rank-if-formula

 

[CalcSux78]

Excel 2010

	A	B	C	D	E
1	BRANCH	CLIENT	FILECNT	TIEBREAKER	RANK
2	12	ABRE01	50	50	1
3	12	AEVA01	45	45	2
4	12	NIEX01	46	46
5	12	NIEX02	47	47
6	12	CAI00	29	29	3
7	32	ABRE02	15	15	4
8	32	AEVA01	45	45	1
9	32	NIEX05	9	9
10	32	NIEX07	47	47
11	32	CAVI00	40	40	3
12	32	CAI00	40	40.1	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =C2+(COUNTIFS(A$2:A2,A2,C$2:C2,C2)-1)*0.1 E2 =IF(LEFT(B2,4)="NIEX","",COUNTIFS(A:A,A2,D:D,">"&D2,B:B,"<>NIEX*")+1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[janetk411]

Thank you! Thank you! Thank you! I really appreciate your assistance