Unique Rank - Dynamic

[The Gent]

Good day,

I am trying to build a ranking formula into a workbook. I want the ranking to be dynamic across four columns though and I am running into difficulty.

Formula for one column:

=RANK($AE9,$AE$9:$AE$497,0)+COUNTIF($AE$9:AE9,AE9)-1

Formula for four columns, I get stuck after the SUMIFS:

=RANK(SUMIFS($AE10:$AH10,$AE$8:$AH$8,Sheet1!$E$2),$AE$9:$AE$497,0)+COUNTIF($AE$9:AE10,AE10)-1

 

[jasonb75]

I don't think that sumifs will be the right way to go about it. Could you post a small sample please (4 columns, 10 rows) and include the expected results to show how the data in the sample should be ranked.

 

[The Gent]

I don't think that sumifs will be the right way to go about it. Could you post a small sample please (4 columns, 10 rows) and include the expected results to show how the data in the sample should be ranked.

Book1
	C	D	E	F	G	H
3	2021 Actual	Q1	Q2	Q3	Q4	Q1
4	CLIENT1	-	587	-	-	191
5	CLIENT2	24,872	38,222	- 144,451	- 155,036	134
6	CLIENT3	- 756,877	- 646,980	-1,334,495	-1,340,311	481
7	CLIENT4	630,630	255,946	-1,311,873	-1,311,873	18
8	CLIENT5	- 25,295	- 250,519	-1,414,041	-1,211,263	341
9	CLIENT6	- 84,108	- 22,398	- 358,324	- 383,324	399
10	CLIENT7	27,757	40,630	- 41,811	- 41,811	128
11	CLIENT8	-	-	-	-	192
12	CLIENT9	5,400	242,064	-	-	161
13	CLIENT10	-	-	-	-	193
Sheet1

Cell Formulas
Range	Formula
H3	H3	='[Revenue Analytics Dashboard v4.xlsx]HiLo Analysis'!$H$6
D4:G13	D4	=SUM(#REF!)
H4	H4	=RANK($AE4,$AE$9:$AE$497,0)+COUNTIF(D4:$AE$9,D4)-1
H5:H8	H5	=RANK(SUMIFS($AE5:$AH5,$AE$8:$AH$8,'[Revenue Analytics Dashboard v4.xlsx]HiLo Analysis'!$H$6),$AE$9:$AE$497,0)+COUNTIF(D5:$AE$9,D5)-1
H9:H13	H9	=RANK(SUMIFS($AE9:$AH9,$AE$8:$AH$8,'[Revenue Analytics Dashboard v4.xlsx]HiLo Analysis'!$H$6),$AE$9:$AE$497,0)+COUNTIF(D$9:$AE9,D9)-1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H4:H13	Other Type	Color scale		NO
G4:G13	Other Type	Color scale		NO
F4:F13	Other Type	Color scale		NO
E4:E13	Other Type	Color scale		NO
D4:D13	Other Type	Color scale		NO

 

[The Gent]

So in H3, it's referencing a drop down list on another sheet. I want column H (H4 down) to select a column based on H3 and then rank the values.

 

[jasonb75]

I'm not seeing it from the example because you left your non working formulas in there instead of entering the correct expected results as I asked for, this is based purely on the original formual and additional information in post 4 which has given me a better picture of what you are attempting to do.

=RANK(INDEX($AE9:$AH9,,--RIGHT($H$3)),INDEX($AE$9:$AH$497,,--RIGHT($H$3)),0)+COUNTIF(INDEX($AE$9:$AH9,,--RIGHT($H$3)),INDEX($AE9:$AH9,,--RIGHT($H$3)))-1

 

[The Gent]

I'm not seeing it from the example because you left your non working formulas in there instead of entering the correct expected results as I asked for, this is based purely on the original formual and additional information in post 4 which has given me a better picture of what you are attempting to do.

=RANK(INDEX($AE9:$AH9,,--RIGHT($H$3)),INDEX($AE$9:$AH$497,,--RIGHT($H$3)),0)+COUNTIF(INDEX($AE$9:$AH9,,--RIGHT($H$3)),INDEX($AE9:$AH9,,--RIGHT($H$3)))-1

This works perfectly