Adding criteria to dynamic rank formula

[droot]

Hi,

I've found a dynamic rank formula on the web that updates the rank as I filter, but I need to add another level, so it's ranking by country. I don't really understand how the formula works, so I'm unable to edit this myself.

This is the formula that I am using - SUM(IF(SUBTOTAL(103,OFFSET($A$2:$A7,ROW($A$2:$A$7)-ROW($A$2),0,1))>0,IF(A2<$A$2:$A$7,1)))+1

Scores	Country	Global Rank	Rank by Country
10	UK	4
23	USA	3
34	UK	2
2	USA	6
65	UAE	1
3	UAE	5

Any assistance would be much appreciated.

Thanks

 

[droot]

Hi,

I've found a dynamic rank formula on the web that updates the rank as I filter, but I need to add another level, so it's ranking by country. I don't really understand how the formula works, so I'm unable to edit this myself.

This is the formula that I am using - SUM(IF(SUBTOTAL(103,OFFSET($A$2:$A7,ROW($A$2:$A$7)-ROW($A$2),0,1))>0,IF(A2<$A$2:$A$7,1)))+1

Scores	Country	Global Rank	Rank by Country
10	UK	4
23	USA	3
34	UK	2
2	USA	6
65	UAE	1
3	UAE	5

Any assistance would be much appreciated.

Thanks

Book2
	A	B	C	D
1	Scores	Country	Global Rank	Rank by Country
2	10	UK	4
3	23	USA	3
4	34	UK	2
5	2	USA	6
6	65	UAE	1
7	3	UAE	5
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=SUM(IF(SUBTOTAL(103,OFFSET($A$2:$A7,ROW($A$2:$A$7)-ROW($A$2),0,1))>0,IF(A2<$A$2:$A$7,1)))+1

 

[droot]

I've tried using an IF and VLOOKUP to look up the country first but that has not given me the desired results (column D).

dynamic_rank_test.xlsx
	A	B	C	D
1	Scores	Country	Global Rank	Rank by Country
2	10	UK	4	9
3	23	USA	3	8
4	34	UK	2	7
5	2	USA	6	11
6	65	UAE	1	6
7	3	UAE	5	10
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=SUM(IF(SUBTOTAL(103,OFFSET($A$2:$A7,ROW($A$2:$A$7)-ROW($A$2),0,1))>0,IF(A2<$A$2:$A$7,1)))+1
D2:D7	D2	=SUM(IF(VLOOKUP(B2,$B$2:$B$7,1,FALSE)=B2,IF(SUBTOTAL(103,OFFSET($A$2:$A7,ROW($A$2:$A$7)-ROW($A$2),0,1))>0,IF(A2<$A$2:$A$7,1)))+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

How about with a helper column

+Fluff 1.xlsm
	A	B	C	D	E
1	Scores	Country	Global Rank	Rank by Country	Helper
2	10	UK	4	2	1
3	23	USA	3	1	1
4	34	UK	2	1	1
5	2	USA	6	2	1
6	65	UAE	1	1	1
7	3	UAE	5	2	1
Main

Cell Formulas
Range	Formula
C2:C7	C2	=COUNTIFS($A$2:$A$7,">"&A2,$E$2:$E$7,1)+1
D2:D7	D2	=COUNTIFS($B$2:$B$7,B2,$A$2:$A$7,">"&A2,$E$2:$E$7,1)+1
E2:E7	E2	=SUBTOTAL(103,A2)

 

[droot]

Yes, this works very well. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.