Countifs and wildcards to rank 2 criteria based on a job title in one column and Percentage in the other.

[stephanini]

I'm trying to rank a group with varying job titles in column E, and a % in column J. Using wildcards to combine the CSR I & CSR II in the same ranked group, however, when using the following formula, it appears that the wildcards aren't working and it's still ranking them based on the 2 different job titles instead of the ranking either job title together.

=IF(E91&"*"=E91&"*",COUNTIFS($E:$E,$E91&"*",$J:$J,">"&J91)+1,"")

 

[jasonb75]

What are you trying to do with the first part?

E91&"*"=E91&"*"makes no sense because you are comparing E91 to E91 and you can't use wildcards in logical tests.

Best guess without the expected results to verify would be

=COUNTIFS($E:$E,$E91&"*",$J:$J,">"&J91)+COUNTIFS($E$91:$E91,$E91&"*",$J$91:$J91,">"&J91)

Although I should point out that the wildcards will only work one way, CSR II will be included in counts where the current row contains CSR I but not the other way around.
For that to work, you would need to split the string in E91 with something like

=COUNTIFS($E:$E,LEFT($E91,3)&"*",$J:$J,">"&J91)+COUNTIFS($E$91:$E91,LEFT($E91,3)&"*",$J$91:$J91,">"&J91)

but again depending on other characteristics of the data that may not be visible from your image, this may not work for everything.