rank for non-numeric, needed for sorting names

[Neomatrix]

Hi,
I am trying to get ranking for non-numerics to show position for A-Z ranking, countif is not working if I have two of the same or more, and it is not working if I have blank cells below as well. I need this for sorting in another table to get A-Z sorted names.
Final result should be like this in A column to mark position on ranking:

 

[Domenic]

Try the following formula, which will work in all versions of Excel...

A3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF($B$3:$B$12<B3,1))+COUNTIF($B$3:$B3,B3)

Book1
	A	B	C	D	E
1
2	Rank	Names
3	6	HORATIUS
4	1	24 HORAS
5	9	NIKE
6	2	A23 DILL
7	10	ZEBAR
8	5	GHUTT
9	8	MARJORIE
10	7	KUBEN
11	3	ADOLF
12	4	ADOLF
13
14
15
Sheet1

Cell Formulas
Range	Formula
A3:A12	A3	=SUM(IF($B$3:$B$12<B3,1))+COUNTIF($B$3:$B3,B3)
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope this helps!

 

[Skyybot]

Book1
	A	B	C	D	E
1	Name	Rank		Numeric Rank
2	Boris	9		Akbar	99
3	Sam	2		Bob	22
4	Bob	22		Boris	9
5	Sam	1		Josh	12
6	Jude	50		Jude	50
7	Akbar	99		Julie	98
8	Julie	98		Sam	1
9	Josh	12		Sam	2
Sheet1

Cell Formulas
Range	Formula
D2:E9	D2	=SORT(SORTBY(A2:B9,B2:B9))
Dynamic array formulas.

 

[myall_blues]

Another option, for 365 or for other versions.

Book1
	A	B	C	D
1
2	365		Other versions
3	6	Horatius	6
4	1	24 Horas	1
5	9	Nike	9
6	2	A23 Dill	2
7	10	Zebar	10
8	5	Ghutt	5
9	8	Marjorie	8
10	7	Kuben	7
11	3	Adolf	3
12	4	Adolf	4
13	11		11
14	12		12
15	13		13
Sheet1

Cell Formulas
Range	Formula
A3:A15	A3	=LET(_a,COUNTIF($B$3:$B$13,"<"&B3)+(COUNT($B$3:$B$13)*ISTEXT(B3))+COUNTIF($B$3:B3,B3),IF(_a=0,_a+ROW()-2,_a))
C3:C15	C3	=IF(COUNTIF($B$3:$B$13,"<"&B3)+(COUNT($B$3:$B$13)*ISTEXT(B3))+COUNTIF($B$3:B3,B3)=0,COUNTIF($B$3:$B$13,"<"&B3)+(COUNT($B$3:$B$13)*ISTEXT(B3))+COUNTIF($B$3:B3,B3)+ROW()-2,COUNTIF($B$3:$B$13,"<"&B3)+(COUNT($B$3:$B$13)*ISTEXT(B3))+COUNTIF($B$3:B3,B3))

 

[Cubist]

Another 2021 or later option:

Book3
	A	B
1
2	365
3	6	Horatius
4	1	24 Horas
5	9	Nike
6	2	A23 Dill
7	10	Zebar
8	5	Ghutt
9	8	Marjorie
10	7	Kuben
11	3	Adolf
12	4	Adolf
Sheet13

Cell Formulas
Range	Formula
A3:A12	A3	=XMATCH(B3,SORT($B$3:$B$12))+COUNTIF($B$3:B3,B3)-1

 

[Neomatrix]

Try the following formula, which will work in all versions of Excel...

A3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF($B$3:$B$12<B3,1))+COUNTIF($B$3:$B3,B3)

Book1
	A	B	C	D	E
1
2	Rank	Names
3	6	HORATIUS
4	1	24 HORAS
5	9	NIKE
6	2	A23 DILL
7	10	ZEBAR
8	5	GHUTT
9	8	MARJORIE
10	7	KUBEN
11	3	ADOLF
12	4	ADOLF
13
14
15
Sheet1

Cell Formulas
Range	Formula
A3:A12	A3	=SUM(IF($B$3:$B$12<B3,1))+COUNTIF($B$3:$B3,B3)
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope thi

Try the following formula, which will work in all versions of Excel...

A3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF($B$3:$B$12<B3,1))+COUNTIF($B$3:$B3,B3)

Book1
	A	B	C	D	E
1
2	Rank	Names
3	6	HORATIUS
4	1	24 HORAS
5	9	NIKE
6	2	A23 DILL
7	10	ZEBAR
8	5	GHUTT
9	8	MARJORIE
10	7	KUBEN
11	3	ADOLF
12	4	ADOLF
13
14
15
Sheet1

Cell Formulas
Range	Formula
A3:A12	A3	=SUM(IF($B$3:$B$12<B3,1))+COUNTIF($B$3:$B3,B3)
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope this helps!

Thanks, but it is not working on blanks