Ranking Numbers With Criteria

[austinandreikurt]

I have the set of numbers from A1 to A34:

2

0

1

1

3

3

0

3

1

0

1

2

0

0

1

0

2

3

1

2

2

2

2

1

4

3

2

0

5

1

3

4

2

3

What I would like is to rank these numbers including the duplicates. But, for duplicate ones, I am using the numbers in Row 3 which is C3 to C34 to determine the rank of duplicates:

5

0

3

5

6

7

0

10

1

0

4

5

0

0

5

0

7

9

4

6

6

5

5

2

13

9

8

0

14

4

8

17

4

8

I used this formula "=RANK(A1,$A$1:$AH$1,0)+COUNTIF($A$1:A1,A1)-1" but this one will not consider the criteria set on Row 3

 

[Sulprobil]

I suggest to google for
stable sort criteria sumproduct sulprobil
and to apply the more complex example.

 

[Phuoc]

Try this:

=RANK(A1,$A$1:$AH$1,0)+COUNTIFS($A$1:$AH$1,A1,$A$3:$AH$3,">"&A3)

 

[austinandreikurt]

Hi Phuoc, It doesn't work properly, like for the #4 with 2 instances, the one with 17 as criteria became 2nd in rank and the one with 13 as criteria because 3rd in rank. It should be the other way around because 13 is lower than 17.

 

[Sulprobil]

Enter into E1

MrExcel_Ranking_with_criteria.xlsx
	E
1	12
Tabelle1

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(A1<$A$1:$A$34))+SUMPRODUCT(--(A1=$A$1:$A$34),--(C1>$C$1:$C$34))+SUMPRODUCT(--(A1=$A$1:$A1),--(C1=$C$1:$C1))

and copy down.

 

[austinandreikurt]

Wow. Sulprobil You are a Rockstar. I can't believe its possible. THANK YOU. This solve my problem!

 

[Phuoc]

Maybe lie this:

=RANK(A1,$A$1:$AH$1,0)+COUNTIF($A$1:A1,A1)-1+COUNTIFS($A$1:$AH$1,A1,$A$3:$AH$3,"<"&A3)

 

[Sulprobil]

Maybe lie this:

=RANK(A1,$A$1:$AH$1,0)+COUNTIF($A$1:A1,A1)-1+COUNTIFS($A$1:$AH$1,A1,$A$3:$AH$3,"<"&A3)

The funny thing is: as soon as you use RANK, it's only numerical values you can rank. With COUNTIF(S) and SUMPRODUCT you can rank both strings and numbers.

 

[Peter_SSs]

I can't believe its possible.

Seems like you have the result that you want (& there is confusion about whether your data is vertical or horizontal) but if this is true ...

I am using the numbers in ... C3 to C34 to determine the rank of duplicates:

.. just trying to understand why the green & yellow duplicates, with identical values in column C get ranked differently by 1 and the blue identical values with different values in column C also get ranked differently by 1? (Sulprobil's formula in column C)

20 09 04.xlsm
	A	B	C	D	E
1	2		5		12
2	0		0		28
3	1		3		22
4	1		5		26
5	3		6		4
6	3		7		5
7	0		0		29
8	3		10		10
9	1		1		20
10	0		0		30
11	1		4		23
12	2		5		13
13	0		0		31
14	0		0		32
15	1		5		27
16	0		0		33
17	2		7		18
18	3		9		8
19	1		4		24
20	2		6		16
21	2		6		17
22	2		5		14
23	2		5		15
24	1		2		21
25	4		13		2
26	3		9		9
27	2		8		19
28	0		0		34
29	5		14		1
30	1		4		25
31	3		8		6
32	4		17		3
33	2		4		11
34	3		8		7
Rank

Cell Formulas
Range	Formula
E1:E34	E1	=SUMPRODUCT(--(A1<$A$1:$A$34))+SUMPRODUCT(--(A1=$A$1:$A$34),--(C1>$C$1:$C$34))+SUMPRODUCT(--(A1=$A$1:$A1),--(C1=$C$1:$C1))

 

[austinandreikurt]

Hi Peter, Yes, sorry for the confusion on the data whether horizontal or vertical. It can still be both as I will just adjust the formula. For the Green and Yellow items you have mentioned, yes they will have to be ranked as the formula says because is should recognized the 1st instance of the same criteria as the higher rank which is similar in the Row 18 and 26 where Row 18 comes first than Row 26. The same for Row 31 and 34.