Continuous Rank by Designation and without ties

[hitgad]

Hi Team,

I have the below table (Column A to C). I want the outcome as per last column. Any formula where for below criteria-
1) It should first Rank all MDs (without ties).
2) After finishing ranking for MDs, it should then continue Rank for DIRs (again without ties)

Column A	Column B	Column C	I want ranking as per below
Name	Title	Calls	Rank
Peter	MD	200	1
John	DIR	190	4
Bill	DIR	170	5
Jake	MD	180	2
Christina	DIR	170	6
Bob	MD	180	3

Thanks
HG

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this works for you.

22 10 20.xlsm
	A	B	C	D
1	Name	Title	Calls	Rank
2	Peter	MD	200	1
3	John	DIR	190	4
4	Bill	DIR	170	5
5	Jake	MD	180	2
6	Christina	DIR	170	6
7	Bob	MD	180	3
Rank

Cell Formulas
Range	Formula
D2:D7	D2	=COUNTIFS(B$2:B$7,B2,C$2:C$7,">"&C2)+COUNTIFS(B$1:B1,B2,C$1:C1,C2)+1+IF(B2="MD",0,COUNTIF(B$2:B$7,"MD"))

 

[hitgad]

Welcome to the MrExcel board!

See if this works for you.

22 10 20.xlsm
	A	B	C	D
1	Name	Title	Calls	Rank
2	Peter	MD	200	1
3	John	DIR	190	4
4	Bill	DIR	170	5
5	Jake	MD	180	2
6	Christina	DIR	170	6
7	Bob	MD	180	3
Rank

Cell Formulas
Range	Formula
D2:D7	D2	=COUNTIFS(B$2:B$7,B2,C$2:C$7,">"&C2)+COUNTIFS(B$1:B1,B2,C$1:C1,C2)+1+IF(B2="MD",0,COUNTIF(B$2:B$7,"MD"))

Thanks Peter. This works. But need to refine this more. I have some MD who have "0" calls is there anyway i can skip them and move to DIR?

 

[Peter_SSs]

I have some MD who have "0" calls is there anyway i can skip them and move to DIR?

Like this?

22 10 20.xlsm
	A	B	C	D
1	Name	Title	Calls	Rank
2	Peter	MD	200	1
3	John	DIR	190	4
4	Bill	DIR	170	5
5	Tom	MD	0
6	Jake	MD	180	2
7	Ann	DIR	0	7
8	Christina	DIR	170	6
9	Bob	MD	180	3
Rank

Cell Formulas
Range	Formula
D2:D9	D2	=IF(B2&C2="MD0","",COUNTIFS(B$2:B$9,B2,C$2:C$9,">"&C2)+COUNTIFS(B$1:B1,B2,C$1:C1,C2)+1+IF(B2="MD",0,COUNTIFS(B$2:B$9,"MD",C$2:C$9,">0")))

 

[hitgad]

Like this?

22 10 20.xlsm
	A	B	C	D
1	Name	Title	Calls	Rank
2	Peter	MD	200	1
3	John	DIR	190	4
4	Bill	DIR	170	5
5	Tom	MD	0
6	Jake	MD	180	2
7	Ann	DIR	0	7
8	Christina	DIR	170	6
9	Bob	MD	180	3
Rank

Cell Formulas
Range	Formula
D2:D9	D2	=IF(B2&C2="MD0","",COUNTIFS(B$2:B$9,B2,C$2:C$9,">"&C2)+COUNTIFS(B$1:B1,B2,C$1:C1,C2)+1+IF(B2="MD",0,COUNTIFS(B$2:B$9,"MD",C$2:C$9,">0")))

Thanks Peter. This is Perfect !!. Appreciate the Help

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.