Conditional rank on three criteria

[eiti88]

Hello,

I am having some trouble with the following table. I want to rank each type (there are 5) and speed by price (with cheapest being 1).
I.e. I want to know the rank of a Type 3 with speed 2, with the cheapest being ranked 1.

Any help much appreciated!


Type Cost Speed Braket Rank
Type 3 42.01 € 2
Type 3 48.01 € 2
Type 3 58.01 € 3
Type 3 68.01 € 3
Type 3 88.01 € 4
Type 1 24.95 € 4
Type 5 77.03 € 4
Type 4 117.03 € 4
Type 5 47.35 € 4
Type 1 37.50 € 2
Type 1 47.00 € 3
Type 1 52.00 € 4
Type 1 27.61 € 2
Type 1 47.61 € 4
Type 1 52.61 € 4

 

[AlphaFrog]

Try this...
=COUNTIFS(A:A=A2,C:C,C2,B:B,"<="&B2)

Or this...
=SUMPRODUCT(($A$2:$A$16=A2)*($C$2:$C$16=C2)*($B$2:$B$16<=B2))

 

[wideboydixon]

I think you can use COUNTIFS:

Book1
	A	B	C	D
1	Type	Cost	Speed Braket	Rank
2	Type 3	42.01 €	2	1
3	Type 3	48.01 €	2	2
4	Type 3	58.01 €	3	1
5	Type 3	68.01 €	3	2
6	Type 3	88.01 €	4	1
7	Type 1	24.95 €	4	1
8	Type 5	77.03 €	4	2
9	Type 4	117.03 €	4	1
10	Type 5	47.35 €	4	1
11	Type 1	37.50 €	2	2
12	Type 1	47.00 €	3	1
13	Type 1	52.00 €	4	3
14	Type 1	27.61 €	2	1
15	Type 1	47.61 €	4	2
16	Type 1	52.61 €	4	4
Sheet1

Cell Formulas
Range	Formula
D2	=1+COUNTIFS($A$2:$A$16,$A2,$C$2:$C$16,$C2,$B$2:$B$16,"<"&$B2)

WBD

 

[wideboydixon]

Quick note that although these two solutions are based on the same logic and provide the same values on the example data, if there are any ties on cost then they will not. See below:

Book1
	A	B	C	D	E	F
1	Type	Cost	Speed Braket	COUNTIFS Rank	SUMPRODUCT RANK	SUMPRODUCT RANK 2
2	Type 1	24.95 €	4	1	1	1
3	Type 1	52.00 €	4	2	3	2
4	Type 1	52.00 €	4	2	3	2
5	Type 1	52.61 €	4	4	4	4
Sheet1

Cell Formulas
Range	Formula
D2	=1+COUNTIFS($A$2:$A$5,$A2,$C$2:$C$5,$C2,$B$2:$B$5,"<"&$B2)
E2	=SUMPRODUCT(($A$2:$A$5=A2)*($C$2:$C$5=C2)*($B$2:$B$5<=B2))
F2	=SUMPRODUCT(($A$2:$A$5=$A2)*($C$2:$C$5=$C2)*($B$2:$B$5<$B2))+1

WBD