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

 

[Phuoc]

A little shorter:

=SUMPRODUCT((A1<$A$1:$A$34)+(A1=$A$1:$A$34)*(C1>$C$1:$C$34))+SUMPRODUCT((A1=$A$1:$A1)*(C1=$C$1:$C1))

 

[Peter_SSs]

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

Thanks for the clarification.

A little shorter:

.. and again

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	=RANK(A1,A$1:A$34)+COUNTIFS(A$1:A$34,A1,C$1:C$34,"<"&C1)+COUNTIFS(A$1:A1,A1,C$1:C1,C1)-1